Re: [sqlite] Compute percentage?

2011-06-29 Thread Gilles Ganault
On Wed, 29 Jun 2011 13:21:21 +0200, Roger Andersson 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

Re: [sqlite] Compute percentage?

2011-06-29 Thread Roger Andersson
On 06/29/11 01:01 PM, Cecil Westerhof wrote: > 2011/6/29 Roger Andersson > > > 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

Re: [sqlite] Compute percentage?

2011-06-29 Thread Roger Andersson
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 sqlite-users@sqlite.org

Re: [sqlite] Compute percentage?

2011-06-29 Thread Mr. Puneet Kishor
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

Re: [sqlite] Compute percentage?

2011-06-29 Thread Cecil Westerhof
2011/6/29 Roger Andersson > 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

Re: [sqlite] Compute percentage?

2011-06-29 Thread Roger Andersson
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

Re: [sqlite] Compute percentage?

2011-06-29 Thread Simon Davies
On 29 June 2011 11:34, Gilles Ganault wrote: > On Wed, 29 Jun 2011 11:33:15 +0200, Roger Andersson > wrote: >>SELECT (COUNT(rowid)*100)/(select count(*) from people) FROM people WHERE >>zip="12345"; > > Thanks, that worked: > > SELECT COUNT(*) FROM

Re: [sqlite] Compute percentage?

2011-06-29 Thread Gilles Ganault
On Wed, 29 Jun 2011 11:33:15 +0200, Roger Andersson 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

Re: [sqlite] Compute percentage?

2011-06-29 Thread Oliver Peters
Oliver Peters 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

Re: [sqlite] Compute percentage?

2011-06-29 Thread Oliver Peters
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

Re: [sqlite] Compute percentage?

2011-06-29 Thread Roger Andersson
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

[sqlite] Compute percentage?

2011-06-29 Thread Gilles Ganault
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