COMPUTE BY
-
I've got the following query and everything runs fine until I add in the BY clause after COMPUTE. When I do that I get the following error:
Error:
Column 'os_g.AccountingProcessDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Occurred on Line : 1
What am I missing?
SELECT DATEPART(yy, os_g.AccountingProcessDate) AS [Year],
[Client Type] =
CASE
WHEN (os_g.ClientType = 0) THEN 'New Client'
WHEN (os_g.ClientType = 1) THEN 'Existing Client'
WHEN (os_g.ClientType = 2) THEN 'Strategic Client'
WHEN (os_g.ClientType = NULL) THEN 'Unknown'
END,
COUNT(*) AS [Orders],
SUM(CAST(os_g.OrderTotal AS decimal(18,2))) AS [Revenue]
FROM os_generalinfo os_g
WHERE os_g.AccountingProcessDate >= '1/1/2002'
GROUP BY DATEPART(yy, os_g.AccountingProcessDate),
os_g.ClientType
ORDER BY DATEPART(yy, os_g.AccountingProcessDate),
os_g.ClientType
COMPUTE
SUM (COUNT(*)),
SUM (SUM(CAST(os_g.OrderTotal AS decimal(18,2))))
BY DATEPART(yy, os_g.AccountingProcessDate)
-
I'm not sure why you're getting this error. But on another note, shouldn't this read ELSE 'Unkown' instead of
WHEN (os_g.ClientType = NULL) THEN 'Unknown'
BTW null = something is never true, not false either
.Read this for more details :
-
you have to have an aggregate function on the column that you are selecting (max, min, avg etc...) when you do a "group by"....in other words, the selected column should only return a single value for the group...
**ASCII stupid question, get a stupid ANSI !!!** -
Here's what sushila means. I made two working versions, but they don't seem to provide any usefull results.
Select MIN(DatePart(YYYY, crdate)) as Year, count(*) as Total from dbo.SysObjects
group by DatePart(YYYY, crdate)
ORDER BY Year
compute
SUM (count(*))
by Year
Select dtCounts.Year, dtCounts.Total FROM
(
Select DatePart(YYYY, crdate) as Year, count(*) as Total from dbo.SysObjects
group by DatePart(YYYY, crdate)
) dtCounts
ORDER BY dtCounts.Year
compute
SUM (dtCounts.Total)
by dtCounts.Year
-
Also, here's something else to "chew on" from BOL....
"COMPUTE and COMPUTE BY are supported for backward compatibility. The ROLLUP operator is preferred over either COMPUTE or COMPUTE BY. The summary values generated by COMPUTE or COMPUTE BY are returned as separate result sets interleaved with the result sets returning the detail rows for each group, or a result set containing the totals appended after the main result set. Handling these multiple result sets increases the complexity of the code in an application. Neither COMPUTE nor COMPUTE BY are supported with server cursors, and ROLLUP is. CUBE and ROLLUP generate a single result set containing embedded subtotal and total rows. The query optimizer can also sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE and COMPUTE BY.
When GROUP BY is used without these operators, it returns a single result set with one row per group containing the aggregate subtotals for the group. There are no detail rows in the result set."
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply