Misuse of Grouping?

Question

Post reply

Misuse of Grouping?

franciscotheman

SSC Veteran

Points: 226
More actions
January 8, 2007 at 1:14 pm

#115647

Can anyone explain why this code returns errors:
DECLARE @d1 datetime, @d2 datetime
SELECT @d1 = '7/1/2006', @d2 = '12/31/2006'
SELECT
CASE GROUPING(LEFT(determinant, 5)) WHEN 0 THEN LEFT(determinant, 5) ELSE 'All' END,
COUNT(*)
FROM response_master_incident
WHERE determinant like '28%'
AND Response_Date between @d1 and dateadd(ss, -1, dateadd(d, 1, @d2))
GROUP BY LEFT(determinant, 5) WITH ROLLUP
I receive the following;
Server: Msg 8120, Level 16, State 1, Line 16
Column 'response_master_incident.Determinant' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I don't get it because I have included the only non aggregate expression in the Group by clause.
???
My work around is to create an imbedded table. Any guidance would be appreciated. TIA.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

SELECT

CASE GROUPING(deter) WHEN 0 THEN deter ELSE 'All' END,

COUNT(*)

FROM (SELECT LEFT(determinant, 5) as deter

FROM response_master_incident

WHERE determinant like '28%'

AND Response_Date between @d1 and dateadd(ss, -1, dateadd(d, 1, @d2))

) DT

GROUP BY deter WITH ROLLUP

If you need to group by part of the field then your database design is wrong.

You'll always have trouble fixing it "on fly" in your queries unless you fix it once and forever.

_____________
Code for TallyGenerator

franciscotheman SSC Veteran Points: 226 More actions · Answer 2

I wrote an embedded query very much like the one shown. I was expecting someone to identify whether or not it is possible to group by an expression and use that expression within a "grouping" function. I am unable to find any documented prohibition of such and unable to get it to work.

You are correct that the data model is non-normal; however, one must live within the constraints of their environment (and I am datamining from a commercial product).

Sergiy SSC Guru Points: 110206 More actions · Answer 3

Assume you cannot create computed columns on source table.

In this situation I would create a view with

LEFT(determinant, 5) as deter

and perform selects for reports from this view.

_____________
Code for TallyGenerator

jtango SSChasing Mays Points: 643 More actions · Answer 4

hi.

Grouping as well as order by can be used with aggregate functions when using the function in group by phrase as "group by LEFT(determinant, 5),...."

This is probably because the result set called in this case as deter doesn't exist (or is unusable) while doing group aggregating. Ie. function can not call itself recursively or otherwise... I think..

Consider a View, it is a very good idea.

JT

a DBA

RichB SSCrazy Eights Points: 9651 More actions · Answer 5

i usually handle it the lazy way

SELECT

CASE GROUPING(det) WHEN 0 THEN det ELSE 'All' END,

sum(cnt)

from

(

select LEFT(determinant, 5)) as det , count(*) as cnt

FROM response_master_incident

WHERE determinant like '28%'

AND Response_Date between @d1 and dateadd(ss, -1, dateadd(d, 1, @d2))

GROUP BY LEFT(determinant, 5)

)x

group by det

WITH ROLLUP

noeld SSC Guru Points: 96590 More actions · Answer 6

OR Include the "missing" part in the select list as the error clearly says:

SELECT

CASE GROUPING(LEFT(determinant, 5)) WHEN 0 THEN LEFT(determinant, 5) ELSE 'All' END,

LEFT(determinant, 5),

COUNT(*)

FROM response_master_incident

WHERE determinant like '28%'

AND Response_Date between @d1 and dateadd(ss, -1, dateadd(d, 1, @d2))

GROUP BY LEFT(determinant, 5) WITH ROLLUP

Cheers,

* Noel

Sergiy SSC Guru Points: 110206 More actions · Answer 7

Don't think it will work because of GROUPING keyword.

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 8

Allow me to differ:

This is a sample from BOL:

USE pubs

SELECT royalty, SUM(advance) 'total advance',

GROUPING(royalty) 'grp'

FROM titles

GROUP BY royalty WITH ROLLUP

Cheers,

* Noel

franciscotheman SSC Veteran Points: 226 More actions · Answer 9

Noel--

It works with a field, it doesn't work with an expression. My questions are 1)should it work with an expression? and 2)why not?

I have convinced myself that it isn't recursive. Maybe SQL Server just can't do it. I haven't tried it on SS2K5.

I am going to stick with the "lazy way."

Execution speed is a non-issue for me, but I would prefer not to create any objects (other than temporary ones); and I absolutely can not modify the database.

Thanks to all who have responded.

Sergiy SSC Guru Points: 110206 More actions · Answer 10

I don't see any use of LEFT or SUBSTRING function in this example.

So, it's not relevant.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 11

> I am going to stick with the "lazy way."

"lazy way" is slightly modified my query. Just another GROUP BY added.

No value added, this just slows query down and adds another hash table to be created in tempdb.

Do you always choose worst possible way?

_____________
Code for TallyGenerator

franciscotheman SSC Veteran Points: 226 More actions · Answer 12

Sergiy--

I appreciate your thoughtful responses to this question and was particularly impressed with your suggestion of a computed column. Funnily though, no one has answered the question which is "shouldn't this work and why doesn't it?"

Finally, I would like to clarify that I wrote a workaround using an embedded table with only one group by (I didn't notice that the "lazy" example had two) prior to posting the question. By posting more constructively, you can contribute better to the community.

regards,

greg