CODEWITHSUNDEEP
sqlcaseteradata
pyll
pyll

Reputation: 1764

SQL CASE statement to do multiple thens

I am trying to simplify a SQL program which creates unique variables based on the case statement. So, for example, say I have a data set like the following:

Salesperson   Invoice  ItemCount   Sales   Type
Joe           1111       100      500.00   internet
Joe           2222       50       200.00   internet
Joe           3333       200      750.00   store
Cthulhu       4444       10       50.00    internet
Cthulhu       5555       20       100.00   internet

And I want to end up with something like:

Salesperson  InternetItems  InternetSales  StoreItems  StoreSales  TotalItems  TotalSales
Joe             150              700.00      200          750.00     350          1450.00
Cthulhu          30              150.00      .            .          30           150.00

So right now I have something like:

select 
  salesperson, 
  case when Type='internet' then ItemCount else .
    end as InternetItems,
  case when Type='internet' then Sales else .
    end as InternetSales,

....more code....

  sum(ItemCount) as TotalItems,
  sum(Sales) as TotalSales,
 group by 1,2,3....

I'd like to be able to condense this by doing something like:

select 
  salesperson, 
  case when Type='internet' then InternetItems=ItemCount and InternetSales=Sales
    else InternetItems=. and InternetSales=.

Is there someway to do that when creation of multiple variables is involved?

Also, how would the sum statement work? Is it:

calculated(sum(InternetItems)) as InternetItems

Thanks, Pyll

Upvotes: 0

Views: 4118

Answers (2)

dnoeth
dnoeth

Reputation: 60462

There's no way to get multiple results/columns from a single CASE, but it's mainly cut&paste&modify to create those different columns:

select 
  salesperson, 
  sum(case when Type='internet' then ItemCount else 0 end) as InternetItems,
  sum(case when Type='internet' then Sales     else 0 end) as InternetSales,
  sum(case when Type='store'    then ItemCount else 0 end) as StoreItems,
  sum(case when Type='store'    then Sales     else 0 end) as StoreSales,
  sum(ItemCount) as TotalItems,
  sum(Sales) as TotalSales
...
 group by 1

Upvotes: 2

Andrew
Andrew

Reputation: 8703

Not entirely sure I understand, but I think you want to build two conditional statements and then sum each? You can't have a single case statement that produces two output columns, if that makes sense.

If you want to aggregate each, just sum the entire case statement, like so:

SUM (CASE when Type='internet' then ItemCount else ... end) as InternetItems ,...

Upvotes: 1

Related Questions