Skip to content
Advertisement

SQL pivot count and sum 2 columns

I have a table

shopid  times   times2
shop1   5        1
shop2   2        2
shop1   6        1
shop1   1        1
shop2   1        2

Query and I got count correct for column Times

Select  [shop1], [Shop2]
from 
(
  Select  shopid, times
  from table3 
)p
pivot 
(
  count(times)
  for shopid in ([shop1], [Shop2])
)pvt;

Results correct count3 for shop1, count 2 for shop2

shop1   Shop2
  3      2

new query add column times2

Select  [shop1], [Shop2]
from 
(
  Select  shopid, times, times2
  from table3 
)p
pivot 
(
  count(times)
  for shopid in ([shop1], [Shop2])
)pvt;

Results and not count for column times2. Can we count and sum for both columns? Thank you.

shop1   Shop2
3        0
0        2

Expect results

shop1   Shop2
  6      4


Advertisement

Answer

One option is a UNION ALL

Example

Select  [shop1], [Shop2]
from 
(
  Select  shopid, times
  from  table3 
  Union All 
  Select shopid, times2
  from  table3 
)p
pivot 
(
  count(times)
  for shopid in ([shop1], [Shop2])
)pvt;

Returns

shop1   Shop2
6       4

And Yet another option is change the count to a SUM()

Example

Select  [shop1], [Shop2]
from 
(
  Select  shopid, times = IsNull(sign(Times),0)+IsNull(sign(Times2),0)
  from table3 
)p
pivot 
(
  sum(times)
  for shopid in ([shop1], [Shop2])
)pvt;
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement