asked on

SQL ROLLUP

I have the following query:

SELECT DATEPART(yyyy, ReferredOn) AS ReferredOnYear, SimpleName, SUM(Count) AS Count, Outcome
FROM dbo.Utilization
GROUP BY DATEPART(yyyy, ReferredOn), SimpleName, Outcome
HAVING (Outcome = N'Completed')
ORDER BY ReferredOnYear

This makes a nice summary of each SimpleName for each year. I would like to have a summary row for each year after its grouping. I'm not sure how to approach this. I tried a rollup but it didn't look as expected.

Thanks.

lcohan

You mean like this?

SELECT q.ReferredOnYear, q.SimpleName, q.[Count], q.Outcome
FROM
(
      SELECT DATEPART(yyyy, ReferredOn) AS ReferredOnYear, SimpleName, SUM([Count]) AS [Count], Outcome
      FROM dbo.Utilization
      GROUP BY DATEPART(yyyy, ReferredOn), SimpleName, Outcome
      HAVING (Outcome = N'Completed')
      --ORDER BY ReferredOnYear
) q
GROUP BY q.ReferredOnYear
ORDER BY q.ReferredOnYear;

LCNW

ASKER

lcohan,

I'm unable to get that to work. It doesn't identify all of the columns or the table properly.

After fixing the syntax it returns the original data set with no sum rows.

I think grouping sets may be the way to go.

LCNW

ASKER

Grouping Sets appears to work, but when I add the HAVING clause it kills it?

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LCNW

ASKER

I got it working prior and added your WHERE and it is still good.

Why do you have to use WHERE instead of HAVING? Just curious.

That's the exact blog I was using.

LCNW

ASKER

Here's what I used and it works. Thanks.

USE [Database]

SELECT DATEPART(yyyy, ReferredOn) AS ReferredOnYear, ISNULL(SimpleName, 'ALL'), SUM(Count) AS Count, ISNULL(Outcome,'Completed')
FROM dbo.Utilization
WHERE (Outcome = 'Completed')
GROUP BY GROUPING SETS
(
   (
    DATEPART(yyyy, ReferredOn)
    ,SimpleName
    ,Outcome
   ),
   (
    DATEPART(yyyy, ReferredOn)
    
   )
)

Open in new window

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial