SELECT CASE WHEN (Grouping(rename)=1) THEN '--Total--' ELSE rtrim(rename) END AS "Assigned",
count(tem) AS "Pending",
SUM(CASE WHEN rtrim(key) = 'AGENCY' THEN 1 ELSE 0 END) AS "Agency",
SUM(CASE WHEN rtrim(key) = 'User' THEN 1 ELSE 0 END) AS "User",
submittername as Submitter,
receiptdate as Date received
Group by rename with rollup
User needs Total column and even the Submitter and Receipt Date.My issue as submittername and Receipt Date is not an aggregate I cant get that with rollup.How do I have to include that in the query.
If Iam using that with rollup,Iam having 3 rows with each line missing a piece of data that was on the line before it.
Please advise as Iam using SQL 2000 for this not SQL 2005.