Group By Bizarreness

7 Jul 2005 5:52 PM

Mike Labosh

This gives me three records (should be one)

SELECT
Opp_Country_Cd,
'2005 YTD' AS Period,
ReportStatus,
Count(RespKey) AS [Total of Key]
FROM Status
WHERE Opp_Country_Cd = 'Canada'
AND ReportStatus = '020DupsDead'
GROUP BY Opp_Country_Cd, Period, ReportStatus
____________________________________
Canada     2005 YTD     020DupsDead     14
Canada     2005 YTD     020DupsDead     13
Canada     2005 YTD     020DupsDead     93

This gives me the one record that I want:

SELECT
Opp_Country_Cd,
'2005 YTD' AS Period,
ReportStatus,
Count(RespKey) AS [Total of Key]
FROM Status
WHERE Opp_Country_Cd = 'Canada'
AND ReportStatus = '020DupsDead'
GROUP BY Opp_Country_Cd, ReportStatus
____________________________________
Canada     2005 YTD     020DupsDead     120

The only difference between the two statements is whether or not I include
the Period alias in the group by clause. I don't understand for two
reasons:

1. The presence of a static literal in the grouping shouldn't affect the
record count
2. I would expect anything in this select clause that's not in the grouping
should cause the server to bark at me with an error.

What's going on?

--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

7 Jul 2005 6:11 PM

Aaron Bertrand [SQL Server MVP]

> GROUP BY Opp_Country_Cd, Period, ReportStatus

I'm surprised this runs, unless the table also has a column named period or
you have otherwise changed the query you are presenting, since you cannot
use aliases in the GROUP BY clause. You should get:

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Period'.
Server: Msg 164, Level 15, State 1, Line 4
GROUP BY expressions must refer to column names that appear in the select
list.

If you want to have that column in the GROUP BY clause (but I don't think
you do), use:

GROUP BY Opp_Country_Cd, '2005 YTD', ReportStatus

7 Jul 2005 6:23 PM

Mike Labosh

>> GROUP BY Opp_Country_Cd, Period, ReportStatus
>
> I'm surprised this runs, unless the table also has a column named period
> or you have otherwise changed the query you are presenting, since you
> cannot use aliases in the GROUP BY clause.

DOH! Yup, the table has a [Period] column in it.

> If you want to have that column in the GROUP BY clause (but I don't think
> you do), use:
>
> GROUP BY Opp_Country_Cd, '2005 YTD', ReportStatus

That gives me this:

Server: Msg 164, Level 15, State 1, Line 9
GROUP BY expressions must refer to column names that appear in the select
list.

In any case, I have the one version of the select statement that gets me
what I want, I was just scratching my head about why the extra item in the
grouping was affecting the records. Obvious behavior once I bang my head a
few times and realize I aliased an expression with a column name. duh.

--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

7 Jul 2005 6:31 PM

Alejandro Mesa

Mike,

You can group by a column that is not in the select list, like:

use northwind
go

select
    orderid,
    count(*)
from
    dbo.orders
group by
    orderid, year(orderdate)
go

and because the "group by" clause is processes first (see link attached), it
is always referencing a column instead the alias. It should be a column in
this table, named [Period] and grouping by "Opp_Country_Cd, Period,
ReportStatus" is not the same as grouping by "Opp_Country_Cd, ReportStatus".

In this lin, look for a msg from Joe Celko, about "how a SELECT works in
SQL".
http://www.dbforums.com/archive/index.php/t-1145784.html

AMB

Show quote

"Mike Labosh" wrote:

> This gives me three records (should be one)
>
> SELECT
> Opp_Country_Cd,
> '2005 YTD' AS Period,
> ReportStatus,
> Count(RespKey) AS [Total of Key]
> FROM Status
> WHERE Opp_Country_Cd = 'Canada'
> AND ReportStatus = '020DupsDead'
> GROUP BY Opp_Country_Cd, Period, ReportStatus
> ____________________________________
> Canada     2005 YTD     020DupsDead     14
> Canada     2005 YTD     020DupsDead     13
> Canada     2005 YTD     020DupsDead     93
>
> This gives me the one record that I want:
>
> SELECT
> Opp_Country_Cd,
> '2005 YTD' AS Period,
> ReportStatus,
> Count(RespKey) AS [Total of Key]
> FROM Status
> WHERE Opp_Country_Cd = 'Canada'
> AND ReportStatus = '020DupsDead'
> GROUP BY Opp_Country_Cd, ReportStatus
> ____________________________________
> Canada     2005 YTD     020DupsDead     120
>
>
> The only difference between the two statements is whether or not I include
> the Period alias in the group by clause. I don't understand for two
> reasons:
>
> 1. The presence of a static literal in the grouping shouldn't affect the
> record count
> 2. I would expect anything in this select clause that's not in the grouping
> should cause the server to bark at me with an error.
>
> What's going on?
> --
> Peace & happy computing,
>
> Mike Labosh, MCSD
>
> "Mr. McKittrick, after very careful consideration, I have
> come to the conclusion that this new system SUCKS."
> -- General Barringer, "War Games"
>
>
>