sparkplug
asked on
ROLL UP query with DATEPART grouping
Hi,
I have the following query for grouping records by Issuer and Year.
SELECT
CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
END AS Issuer,
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')
END AS YearRaised,
COUNT(*) AS NumberOfRecords
FROM tblCAIR
GROUP BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised) WITH ROLLUP
ORDER BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised)
This should produce a roll-up table of record counts. It results, however, in the following error:
"Column 'tblCAIR.DateRaised' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.".
This error is generated by the line "ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')" which is there to deal with NULL values in the resultset. If I remove this line the error goes but as expected I get a list of NULLs in the YearRaised column.
Does anyone know how to fix this query?
The intended output is similar to the following:
Issuer YearRaised NumberOfRecords
-------------------------- ---------- ---
(ALL) (ALL) 12
Some Name (ALL) 6
Some Name 2002 2
Some Name 2003 2
Some Name UNKNOWN 1
Other Name (ALL) 4
Other Name 2002 1
Other Name 2003 1
Other Name UNKNOWN 2
UNKNOWN UNKNOWN 2
Issuer is nvarchar(100)
DateRaised is smalldatetime
I'm using SQL Server 7 SP3
Thanks in advance,
>S'Plug<
I have the following query for grouping records by Issuer and Year.
SELECT
CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
END AS Issuer,
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.DateRaised,
END AS YearRaised,
COUNT(*) AS NumberOfRecords
FROM tblCAIR
GROUP BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised) WITH ROLLUP
ORDER BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised)
This should produce a roll-up table of record counts. It results, however, in the following error:
"Column 'tblCAIR.DateRaised' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.".
This error is generated by the line "ELSE ISNULL(tblCAIR.DateRaised,
Does anyone know how to fix this query?
The intended output is similar to the following:
Issuer YearRaised NumberOfRecords
--------------------------
(ALL) (ALL) 12
Some Name (ALL) 6
Some Name 2002 2
Some Name 2003 2
Some Name UNKNOWN 1
Other Name (ALL) 4
Other Name 2002 1
Other Name 2003 1
Other Name UNKNOWN 2
UNKNOWN UNKNOWN 2
Issuer is nvarchar(100)
DateRaised is smalldatetime
I'm using SQL Server 7 SP3
Thanks in advance,
>S'Plug<
ASKER
Hi Sharper,
Didn't make any difference. I'm kind of confused about where this thought was going though?
>S'Plug<
Didn't make any difference. I'm kind of confused about where this thought was going though?
>S'Plug<
I was thinking that the previous case works, but the else doesn't. The case uses datepart and the else dosn't, so I was trying to get the datepart into the else to see if it made a difference.
when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else 'UNKNOWN'
end as YearRaised
else 'UNKNOWN'
end as YearRaised
You should be able to GROUP BY the entire expression, for example:
SELECT
CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
END AS Issuer,
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')
END AS YearRaised,
COUNT(*) AS NumberOfRecords
FROM tblCAIR
GROUP BY
CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
END,
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.DateRaised, 'UNKNOWN')
END WITH ROLLUP
ORDER BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised)
SELECT
CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
END AS Issuer,
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.DateRaised,
END AS YearRaised,
COUNT(*) AS NumberOfRecords
FROM tblCAIR
GROUP BY
CASE WHEN (GROUPING(tblCAIR.Issuer) = 1) THEN '(ALL)'
WHEN tblCAIR.Issuer='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.Issuer, 'UNKNOWN')
END,
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(tblCAIR.DateRaised,
END WITH ROLLUP
ORDER BY tblCAIR.Issuer, DATEPART(year, tblCAIR.DateRaised)
ASKER
Nice try Scott. That looked like it would work, however I get this error message: "Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause." I guess the CASE statement is classed as a subquery.
Sharper,
What I am looking for is more like:
when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else tblCAIR.DateRaised
which is what the ISNULL function does. This however does not work either. The problem is something to do with using the GROUP BY clause.
>S'Plug<
Sharper,
What I am looking for is more like:
when DATEPART(year, tblCAIR.DateRaised) is null then 'UNKNOWN'
else tblCAIR.DateRaised
which is what the ISNULL function does. This however does not work either. The problem is something to do with using the GROUP BY clause.
>S'Plug<
The GROUPING is the problem; I should have realized that earlier. I'm not sure how to fix it within SQL's rules. I'll keep thinking about it, though.
ASKER
OK, so this is difficult one. Upping the points...
>S'Plug<
>S'Plug<
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
with the tiny bit of test data I created anyway
ASKER
Cool!
That does the job. Although I can't figure out exactly why. I guess it needed an aggregate on an int in their somewhere.
I've managed to compress it slightly:
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(cast(SUM(datepart(y ear, tblCAIR.DateRaised))/count (*) as varchar), 'UNKNOWN')
END AS YearRaised,
This seems to work just the same.
Some well-deserved points go to Sharper.
Thanks to all for the help,
>S'Plug<
That does the job. Although I can't figure out exactly why. I guess it needed an aggregate on an int in their somewhere.
I've managed to compress it slightly:
CASE WHEN (GROUPING(DATEPART(year, tblCAIR.DateRaised)) = 1) THEN '(ALL)'
WHEN DATEPART(year, tblCAIR.DateRaised)='' THEN 'UNKNOWN'
ELSE ISNULL(cast(SUM(datepart(y
END AS YearRaised,
This seems to work just the same.
Some well-deserved points go to Sharper.
Thanks to all for the help,
>S'Plug<
Thanks,
Basically, It was aggregating on the year. Since I knew the number or record in the year. I summed the datevalue and then divided by the count to return the original value. Like I said, "ugly, but it works."
You mod actually makes it work more smoothly since all you want is the year. If you want the full date, I think you would have to use my ugly mess.
Basically, It was aggregating on the year. Since I knew the number or record in the year. I summed the datevalue and then divided by the count to return the original value. Like I said, "ugly, but it works."
You mod actually makes it work more smoothly since all you want is the year. If you want the full date, I think you would have to use my ugly mess.
WHEN DATEPART(year, ISNULL(tblCAIR.DateRaised,
Just an untested thought.