> 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
