Compute percentage?

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Compute percentage?

Gilles Ganault-3
Hello

        Using a table that lists people and the zipcode where they live, I
need to compute the percentage of those living in, say, NYC.

I googled for this, but I'm not sure how to do this in SQLite.

I wonder if it's done through a sub-query or maybe some temporary
variable?

This computes the absolute:
SELECT COUNT(rowid) FROM people WHERE zip="12345";

Thank you.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Roger Andersson-2
  On 06/29/11 11:22 AM, Gilles Ganault wrote:

> Hello
>
> Using a table that lists people and the zipcode where they live, I
> need to compute the percentage of those living in, say, NYC.
>
> I googled for this, but I'm not sure how to do this in SQLite.
>
> I wonder if it's done through a sub-query or maybe some temporary
> variable?
>
> This computes the absolute:
> SELECT COUNT(rowid) FROM people WHERE zip="12345";
>
What about

SELECT (COUNT(rowid)*100)/(select count(*) from people) FROM people WHERE zip="12345";
/Roger


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

oliver1804
In reply to this post by Gilles Ganault-3
Gilles Ganault <gilles.ganault@...> writes:


SELECT a.zip, a.cnt_people_in_town, b.cnt_all_people
(
SELECT zip, COUNT(zip) AS cnt_people_in_town
FROM people
GROUP BY zip
) a
CROSS JOIN
(
SELECT COUNT(zip)
FROM people AS cnt_all_people
) b
;

percent for you

greetings
oliver


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

oliver1804
Oliver Peters <oliver.pet@...> writes:

I definitely need glasses for my glasses

 AS cnt_all_people belongs after COUNT(zip)

so this is correct

SELECT a.zip, a.cnt_people_in_town, b.cnt_all_people
(
SELECT zip, COUNT(zip) AS cnt_people_in_town
FROM people
GROUP BY zip
) a
CROSS JOIN
(
SELECT COUNT(zip) AS cnt_all_people
FROM people
) b
;

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Gilles Ganault-3
In reply to this post by Roger Andersson-2
On Wed, 29 Jun 2011 11:33:15 +0200, Roger Andersson
<[hidden email]> wrote:
>SELECT (COUNT(rowid)*100)/(select count(*) from people) FROM people WHERE zip="12345";

Thanks, that worked:

SELECT COUNT(*) FROM people;
400599

SELECT COUNT(*) FROM people WHERE zip="12345";
12521

SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) FROM people
WHERE zip="12345";
3

Is it possible to display the number with decimals instead of an
integer?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Simon Davies
On 29 June 2011 11:34, Gilles Ganault <[hidden email]> wrote:

> On Wed, 29 Jun 2011 11:33:15 +0200, Roger Andersson
> <[hidden email]> wrote:
>>SELECT (COUNT(rowid)*100)/(select count(*) from people) FROM people WHERE zip="12345";
>
> Thanks, that worked:
>
> SELECT COUNT(*) FROM people;
> 400599
>
> SELECT COUNT(*) FROM people WHERE zip="12345";
> 12521
>
> SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) FROM people
> WHERE zip="12345";
> 3
>
> Is it possible to display the number with decimals instead of an
> integer?

http://www.sqlite.org/lang_expr.html#castexpr

>

Rgds,
Simon
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Roger Andersson-2
In reply to this post by Gilles Ganault-3
  On 06/29/11 12:34 PM, Gilles Ganault wrote:

> Thanks, that worked:
> SELECT COUNT(*) FROM people;
> 400599
>
> SELECT COUNT(*) FROM people WHERE zip="12345";
> 12521
>
> SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) FROM people
> WHERE zip="12345";
> 3
>
> Is it possible to display the number with decimals instead of an
> integer?
SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM
people),2) FROM people WHERE zip="12345";
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Cecil Westerhof-5
2011/6/29 Roger Andersson <[hidden email]>

> SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM
> people),2) FROM people WHERE zip="12345";
>

Would it not be better to do the CAST on the second SELECT? Then there is
only one CAST needed. In this case it does not matter much, but in the
general case it could.
Or maybe even better instead of doing * 100 in the first select, do * .01 in
the second.

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Puneet Kishor-2
In reply to this post by Roger Andersson-2

On Jun 29, 2011, at 6:53 AM, Roger Andersson wrote:

>  On 06/29/11 12:34 PM, Gilles Ganault wrote:
>> Thanks, that worked:
>> SELECT COUNT(*) FROM people;
>> 400599
>>
>> SELECT COUNT(*) FROM people WHERE zip="12345";
>> 12521
>>
>> SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) FROM people
>> WHERE zip="12345";
>> 3
>>
>> Is it possible to display the number with decimals instead of an
>> integer?
> SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM
> people),2) FROM people WHERE zip="12345";

SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) * 1.00 AS percentage
FROM people
WHERE zip="12345";

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Roger Andersson-2
  On 06/29/11 01:02 PM, Mr. Puneet Kishor wrote:
> SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) * 1.00 AS percentage
> FROM people
> WHERE zip="12345";
Seems to always return .0 ?
/Roger
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Roger Andersson-2
In reply to this post by Cecil Westerhof-5
  On 06/29/11 01:01 PM, Cecil Westerhof wrote:
> 2011/6/29 Roger Andersson <[hidden email] <mailto:[hidden email]>>
>
>     SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM
>     people),2) FROM people WHERE zip="12345";
>
>
> Would it not be better to do the CAST on the second SELECT? Then there
> is only one CAST needed. In this case it does not matter much, but in
> the general case it could.
I can only see one CAST ;-)
> Or maybe even better instead of doing * 100 in the first select, do *
> .01 in the second.
>
:) No CAST needed
SELECT round((COUNT(rowid))/(SELECT COUNT(*)*0.01 FROM people),2) FROM
people WHERE zip="12345";

/Roger
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Compute percentage?

Gilles Ganault-3
On Wed, 29 Jun 2011 13:21:21 +0200, Roger Andersson
<[hidden email]> wrote:
>:) No CAST needed
>SELECT round((COUNT(rowid))/(SELECT COUNT(*)*0.01 FROM people),2) FROM
>people WHERE zip="12345";

Thanks everyone.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users