CASE expression in a group by (dynamic group by)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CASE expression in a group by (dynamic group by)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-01 : 08:38:01

Geraydo writes "I have a stored procedure that provides the data necessary to measure the performance of different ads we have on sites based on impression or click delivery. The impressions and clicks are recorded on a daily basis.
My intention is to provide the user with several options to breakdown this data for analysis to these different levels: daily and weekly (and probably monthly as well).

I have calculated the start date in the case of a weekly breakdown and grouped by this calculated expression but as soon as I try ordering by this calculated expression, I get the following error:

'csdDate is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.' I do however include it in a CASE statement in the GROUP BY.

Could you please give me a reason, if possible, why SQL Server is complaining about this and a possible solution.

Here is a snippet of my stored procedure with the following INPUT parameters:

@perfrmBase 'Metric to base performance on - Impressions/Clicks
@breakdown ' Daily, Weekly (Monthly)
@sortby 'coloumn to sort by
@sortorder 'ascending or descending

SELECT csdSiteId AS ItemId, siteName AS Item,
CASE @breakdown
WHEN 'D' THEN csdDate
ELSE DATEADD(d, -(DATEPART(dw, csdDate) - 1), csdDate)
-- start date/period when weekly breakdown
END AS DatePeriod,

SUM(csdImpressionsDelivered) AS Impressions, SUM(csdClicksRecorded) AS Clicks,
CASE
WHEN (SUM(csdImpressionsDelivered) <> 0) THEN CAST(SUM (csdClicksRecorded) AS decimal)/CAST(SUM(csdImpressionsDelivered) AS decimal)*100.0
WHEN (SUM(csdImpressionsDelivered) = 0) THEN 0.0
END AS ClickRate

FROM campaignSpotlightData INNER JOIN site ON csdSiteId = siteId
WHERE ...

...GROUP BY csdSiteId, siteName,

CASE @breakdown
WHEN 'D' THEN csdDate
ELSE DATEADD(d, -(DATEPART(dw, csdDate) - 1), csdDate)
END

ORDER BY
CASE
WHEN (@sortorder = 'D' OR @sortorder IS NULL) THEN NULL
ELSE
CASE
WHEN (@sortby IS NULL) THEN
CASE @perfrmBase
WHEN 'I' THEN STR(SUM(csdImpressionsDelivered), 10)
WHEN 'C' THEN STR(SUM(csdClicksRecorded), 10)
END
ELSE
CASE @sortby WHEN 'SI' THEN STR(csdSiteId, 10)
WHEN 'S' THEN dbo.site.siteName
WHEN 'I' THEN STR(SUM(csdImpressionsDelivered), 10)
WHEN 'C' THEN STR(SUM(csdClicksRecorded), 10) WHEN 'CR' THEN --Click RAte Calculation as above

--The following statement is the one which, when included,
--gives me the error mentioned above:

WHEN 'D' THEN CASE @breakdown WHEN 'D' THEN STR(CAST
(csdDate AS bigint), 10)
ELSE STR(CAST(DATEADD(d, -(DATEPART(dw, csdDate) -
1), csdDate) AS bigint), 10)
END

END

END
END ASC,

CASE
WHEN @sortorder = 'A' THEN NULL
ELSE
CASE
WHEN (@sortby IS NULL) THEN ... --same as in ASC block ELSE
CASE @sortby ... --same as in ASC block
END DESC"

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-06-01 : 11:32:16

Group by the case statement in your select clause.

Mark

geraydo
Starting Member

1 Post

Posted - 2005-06-10 : 09:08:34

I have done that (check the CASE expression in the GROUP BY in original post). SQL Server is happy when I include the CASE expression in the SELECT and the same CASE expression in the GROUP BY, but as soon as I include it in the ORDER BY as well, it complains.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources