Suggestion for Query

  • 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!

  • 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

  • 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

     

  • 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]

  • 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