Interesting....
See fully worked examples below (I've fixed the totaling issue in my example, plus added another one using a group by with rollup and added @ScottPletcher's example
And they are different (by 1) (look at the "Word of Mouth" row in the final two outputs)
Why?
Because of the data...
"Compliance" vs "Complaince"...
You need to decide which output is "correct"!
CREATE TABLE #Forumtest (
T_ID INTEGER,
T_Firstname VARCHAR(30),
T_Lastname VARCHAR(30),
T_Status VARCHAR(30),
T_Source VARCHAR(30)
)
INSERT INTO #Forumtest (T_ID, T_Firstname, T_Lastname, T_Status, T_Source) VALUES
(1000, 'Jane', 'Doe', 'Live', 'Website'),
(1001, 'John', 'Doe', 'Live', 'Word Of Mouth'),
(1002, 'Tina', 'Yip', 'Live', 'Website'),
(1003, 'Dave', 'Liu', 'Live', 'Facebook'),
(1004, 'Ryan', 'Hay', 'Compliance', 'Unknown'),
(1005, 'Matt', 'See', 'Complaince', 'Word Of Mouth'),
(1006, 'Josh', 'Tob', 'Compliance', 'Website'),
(1007, 'Stef', 'Rae', 'FNR', 'Linked In'),
(1008, 'Ruby', 'Ray', 'FNR', 'Other'),
(1009, 'Soph', 'Bob', 'Pending', 'Other'),
(1010, 'Tomo', 'Tut', 'Pending', 'Website'),
(1011, 'Bill', 'Tuk', 'Booked', 'Website'),
(1012, 'Char', 'Pou', 'Booked', 'Website')
select * from #Forumtest
; with pivotdata as (
select
t_source
, t_status
, 1 as d
from #Forumtest
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked]
from pivotdata
pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p;
; with pivotdata as (
select
t_source
, t_status
, 1 as d
from #Forumtest
)
, pivoted as (
select t_source, [live], [compliance], [FNR], [Pending], [Booked]
from pivotdata
pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select * from pivoted
;
; with pivotdata as (
select
t_source
, t_status
, 1 as d
from #Forumtest
)
, pivoted as (
select t_source, [live], [compliance], [FNR], [Pending], [Booked]
from pivotdata
pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked], [live] + [compliance] + [FNR] + [Pending] + [Booked] as [total] from pivoted
;
; with pivotdata as (
select
t_source
, t_status
, 1 as d
from #Forumtest
)
, pivoted as (
select t_source, [live], [compliance], [FNR], [Pending], [Booked]
from pivotdata
pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked], [live] + [compliance] + [FNR] + [Pending] + [Booked] as [total] from pivoted
union ALL
select 'total', sum([live]), sum([compliance]), sum([FNR]), sum([Pending]), sum([Booked]), sum([live] + [compliance] + [FNR] + [Pending] + [Booked] ) from pivoted
;
; with pivotdata as (
select
t_source
, t_status
, 1 as d
from #Forumtest
)
, pivoted as (
select t_source, [live], [compliance], [FNR], [Pending], [Booked]
from pivotdata
pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select isnull(t_source, 'total'), sum([live]) as live, sum([compliance]) as [compliance], sum([FNR]) as [FNR], sum([Pending]) as [Pending], sum(Booked) as [Booked], sum([live] + [compliance] + [FNR] + [Pending] + [Booked]) as total from pivoted
group by t_source with ROLLUP
SELECT
COALESCE(Source, 'Totals') AS Source,
Live, Compliance, FNR, Pending, Booked, Total
FROM (
SELECT
T_Source AS Source,
SUM(CASE WHEN T_Status = 'Live' THEN 1 ELSE 0 END) AS Live,
SUM(CASE WHEN T_Status = 'Compliance' THEN 1 ELSE 0 END) AS Compliance,
SUM(CASE WHEN T_Status = 'FNR' THEN 1 ELSE 0 END) AS FNR,
SUM(CASE WHEN T_Status = 'Pending' THEN 1 ELSE 0 END) AS Pending,
SUM(CASE WHEN T_Status = 'Booked' THEN 1 ELSE 0 END) AS Booked,
SUM(1) AS Total
FROM #Forumtest
GROUP BY T_Source WITH ROLLUP
) AS query1
ORDER BY CASE WHEN Source IS NULL THEN 1 ELSE 0 END, Source