Re: Count(*) Query
SELECT number, count(*) FROM table GROUP BY number > I have a table that has one field that holds numbers 1 thru 9. Can I run a > single query that will give me the count(*) of 1's, count(*) of 2's, etc. or > do I have to run a separate query for each? > > Thanks to all, > > Keith > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Count(*) Query
You're right, I didn't see your query when I wrote this one. -Original Message- From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: Friday, April 04, 2003 9:52 AM To: CF-Talk Subject: RE: Count(*) Query > SELECT Count(NumbersField) AS CountOfField > FROM table1 > GROUP BY NumbersField > > That does it, I tried it. But you only get the Count, so you don't know what it's a count of... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Count(*) Query
> SELECT Count(NumbersField) AS CountOfField > FROM table1 > GROUP BY NumbersField > > That does it, I tried it. But you only get the Count, so you don't know what it's a count of... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Count(*) Query
SELECT Count(NumbersField) AS CountOfField FROM table1 GROUP BY NumbersField That does it, I tried it. Matt Small -Original Message- From: Hawkes, Keith A CIV [mailto:[EMAIL PROTECTED] Sent: Friday, April 04, 2003 7:18 AM To: CF-Talk Subject: Count(*) Query I have a table that has one field that holds numbers 1 thru 9. Can I run a single query that will give me the count(*) of 1's, count(*) of 2's, etc. or do I have to run a separate query for each? Thanks to all, Keith ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Count(*) Query
You could do something like this: SELECT TOP 1 (select count(qty) from items where qty = 1) AS num_1s, (select count(qty) from items where qty = 2) AS num_2s FROM items GROUP BY qty I'm sure there's a more efficient way to do this, but this should work. - Original Message - From: "Hawkes, Keith A CIV" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, April 04, 2003 6:17 AM Subject: Count(*) Query > I have a table that has one field that holds numbers 1 thru 9. Can I run a > single query that will give me the count(*) of 1's, count(*) of 2's, etc. or > do I have to run a separate query for each? > > Thanks to all, > > Keith > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Count(*) Query
> I have a table that has one field that holds numbers 1 thru > 9. Can I run a single query that will give me the count(*) > of 1's, count(*) of 2's, etc. or do I have to run a separate > query for each? Do you want them in one row, or one row per number? The SQL is a lot bigger for one row, but here they are This will give you only 1-9 and one row per number; SELECT number, Count(number) as Counted FROM table GROUP BY number HAVING number BETWEEN 1 AND 9 ORDER BY number This will give you one row with all 9 values; SELECT TOP 1 (SELECT count(number) FROM table WHERE number=1) as Count1, (SELECT count(number) FROM table WHERE number=2) as Count2, (SELECT count(number) FROM table WHERE number=3) as Count3, (SELECT count(number) FROM table WHERE number=4) as Count4, (SELECT count(number) FROM table WHERE number=5) as Count5, (SELECT count(number) FROM table WHERE number=6) as Count6, (SELECT count(number) FROM table WHERE number=7) as Count7, (SELECT count(number) FROM table WHERE number=8) as Count8, (SELECT count(number) FROM table WHERE number=9) as Count9 FROM table HTH ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
