I have a table
x
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;