October 31, 2005 at 1:14 pm
I am querying a table with columns, [CUSTOMERNO], [USERNAME] and [STATE]. State is either 'TX', 'NM' or 'OK'. I need to generate a query that outputs one line per username and one column per state with the count(*) of each. For instance, BOB | 10 | 19 | 27, where Bob has 10 accounts in TX, 19 in NM and 27 in OK.
What would be the ideal way to do this, without changing the design of the table itself?
Thanks!
October 31, 2005 at 1:22 pm
select [USERNAME]
, SUM ( CASE [STATE] WHEN 'TX' then 1 else 0 end ) as TxCount
, SUM ( CASE [STATE] WHEN 'NM' then 1 else 0 end ) as NmCount
, SUM ( CASE [STATE] WHEN 'OK' then 1 else 0 end ) as OKCount
from
group by [USERNAME]
SQL = Scarcely Qualifies as a Language
October 31, 2005 at 1:22 pm
select username
,SUM( CASE WHEN state = 'TX' THEN 1 ELSE 0 END) as TX_count
,SUM( CASE WHEN state = 'OK' THEN 1 ELSE 0 END) as OK_count
,you get the idea
FROM...
GROUP BY username
October 31, 2005 at 1:26 pm
Try this Bob.
SELECT
[USERNAME],
SUM(CASE WHEN [STATE] = 'TX' THEN 1 ELSE 0 END) 'TX',
SUM(CASE WHEN [STATE] = 'NM' THEN 1 ELSE 0 END) 'NM',
SUM(CASE WHEN [STATE] = 'OK' THEN 1 ELSE 0 END) 'OK'
FROM
table_name
GROUP BY
[USERNAME]
October 31, 2005 at 1:55 pm
Thanks to all three of you for posting so quickly! I appreciate it!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply