GROUP BY question

Question

Post reply

GROUP BY question

Jurushia

SSC Eights!

Points: 947
More actions
November 2, 2005 at 9:36 am

#88353

I have a rather large query that I inserted a COUNT(column) in, and now that I've done that I get tons of:
Column 'x' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause
Now I know that if I put every column that is in my SELECT statement in the GROUP BY clause, the error will go away, but my question is, why do I have to do this?
Also, one of the columns is of datatype TEXT, which gives me errors when I put it in the GROUP BY clause. So in this query, I actually can't put every column in the GROUP BY statement. I even tried using CAST to cast the text columns to vartype, but that didn't work.
I can't believe I am getting this good at SQL!
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 1

Create derived table with count/group only and then add the rest of data you need

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

As soon as you put a count() in your query, SQL Server assumes that you are attempting to summarise your data in some way. In particular, it assumes that you are no longer returning individual rows. For example, if your count() returns 3, it means that you have sumarised across three rows in an underlying table/recordset. If you have another column called, say, ID, in your SELECT statement, how does SQL Server know which version of ID to return (from row 1, or row 2, or row 3) without you telling it?

Even if you know that all of the ID values are the same, SQL Server doesn't. You have to use GROUP BY with any columns you do not aggregate in your SELECT.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jurushia SSC Eights! Points: 947 More actions · Answer 3

I thought SQL Server only counted the column you had the COUNT clause operated on. For example,

SELECT id, color, date, type, COUNT(type) AS 'Quantity'

FROM CatalogTable

WHERE date > '01/01/2005'

AND color = 'Green'

Doesn't SQL know that I'm only trying to count the 'type' column?

so I'd have to do this:

SELECT id, color, date, type, COUNT(type) AS 'Quantity'

FROM CatalogTable

WHERE date > '01/01/2005'

AND color = 'Green'

GROUP BY id, color, date, type

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

No it doesn't.

From BOL:

COUNT(*) returns the number of items in a group, including NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

You will see that the key word throughout these definitions is 'GROUP'. You cannot use aggregate functions without some notion of a group. You can, of course, use aggregate functions on their own without a GROUP BY clause, eg:

SELECT COUNT(*) from t1

returns the total number of rows in table t1. Even here, there is a notional group: the entire table.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ian Yates SSCoach Points: 19738 More actions · Answer 5

What Yuri said is the best approach.

But, could you please post the query and what you are trying to accomplish - perhaps we can find a better way of you to do your counting.

Jurushia SSC Eights! Points: 947 More actions · Answer 6

The query works until I try and put the COUNT, SUM, and AVG statements in it. So without the added functions, it works like it should. But I need to modify the query to include totals and averages as well.

SELECT RGnameFirst + ' ' + RGnameLast AS 'Name', COUNT(1) AS 'Adjuster Total', COcity AS 'Client Offce',

WRid AS 'Case #', SRnameLast + ' ' + SRnameFirst AS 'Subject Name',

CONVERT(varchar,WRreceivedDate, 101) AS 'Received Date', WRbillToFileId AS 'Claim #',

CAST(WCvideoAmount AS varchar) AS 'Video Amount',

CAST(WRnotes AS varchar) AS 'Object', CAST(WCnotes AS varchar) AS 'Results',

WRinjury AS 'Injury', WTbudgetAmount AS 'Cost', WTstatusId AS 'Status',

WCtaskAmount, WCbillableAmount AS 'Bill',

COUNT(RGnameLast) AS 'Total Cases',

SUM(WCvideoAmount) AS 'Total Video',

SUM(WTbudgetAmount) AS 'Total Cost',

AVG(WCvideoAmount) AS 'Average Video Length',

AVG(WTbudgetAmount) AS 'Average Cost per Case'

FROM webrequest, reportgenerator, collegeoffice, studentrecord, webtask, webcompleted

WHERE COclientMasterID = 'CM235'

AND WRreceivedDate >= '01/1/2005'

AND WRreceivedDate <= '10/01/2005'

AND WRreportgeneratorId = RGid

AND RGcollegeofficeId = COid

AND WRstudentrecordId = SRid

AND WTlinkItemId = WRid

AND WClinkItemId = WTid

GROUP BY RGnameLast, RGnameFirst, COcity, SRnameLast, SRnameFirst, WRreceivedDate, WRbillToFIleId,

WCvideoAmount, WRinjury, WTbudgetAmount, WTstatusId, WCtaskAmount, WCbillableAmount, WRid,

WRnotes, WCnotes

But whenever I try and run it, I get these errors:

Server: Msg 306, Level 16, State 2, Line 1