GROUP BY clause without an aggregate

Question

GROUP BY clause without an aggregate

robertafricker

Mr or Mrs. 500

Points: 591
More actions
March 12, 2008 at 12:01 am

#187849

Hello all,
This is a minor twist on the other query that I just submitted.
Either my online course is making assumptions, or I am missing something. Here is the info:
The following code shows a GROUP BY clause without an aggregate. This example returns a row for each of the categories. You will notice that although there are two beverages with a unit price of $14.00, you will see only one occurrence, rather than both:
Use Northwind
SELECT CategoryName, UnitPrice
FROM Categories cat JOIN Products pro
ON pro.CategoryID = cat.CategoryID
GROUP BY CategoryName, UnitPrice
ORDER BY CategoryName, UnitPrice
My question is:
1) Where are the "two beverages with a unit price of $14.00..?"
Let me know, gracias!

Viewing 4 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Group by without a distinct has the same effect as DISTINCT. sometimes there are minor differences behid the scenes, but usually not.

When you use GROUP BY, you're telling SQL you only want the unique vaules of the columns in the group by clause, and any others specified will be in some form of aggregate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 2

The two beverages are in the table. The result of the query will be one row for that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

robertafricker Mr or Mrs. 500 Points: 591 More actions · Answer 3

Hi Gail,

You have sent me at least two posts that I want to look at carefully tomorrow. Thank you much for "looking out."...;)

I am especially interested in the WITH CUBE and ROLLUP doc you attached for me, and I will respond tomorrow.

Again, your tag line doesn't fail to make me 😀

Until manana...