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;