1

I am trying to write a query to select the total outbound usage of each pharmacy in my database table for each month.

Here is what I have so far, it outputs the correct data. But I want to eliminate the amount of rows selected

select pharmacyid, count(*) as usage, month(datecalled) as month
from outboundcalldata
where datepart(year, datecalled) = 2014
group by pharmacyid, YEAR(DateCalled), month(datecalled)
order by pharmacyid, month

example of output:

pharmacyid|usage| month
-----------------------
2220000006| 10  |  2
2220000006| 11  |  3
2220000006| 900 |  4
2220000006| 30  |  5
2220000007| 34  |  2
2220000007| 300 |  3
2220000007| 145 |  4

Instead I would like it to output 1 row per pharmacy and a column for each month.

user3562751
  • 263
  • 1
  • 5
  • 15

1 Answers1

1
;WITH CTE AS
(
 select pharmacyid, count(*) as usage, month(datecalled) as [month]
 from outboundcalldata
 where datepart(year, datecalled) = 2014
 group by pharmacyid, YEAR(DateCalled), month(datecalled)
)
SELECT *
FROM CTE C
 PIVOT (SUM(usage)
        FOR [month]
        IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
        )p
M.Ali
  • 67,945
  • 13
  • 101
  • 127