Aggregates and Group BY

Question

Aggregates and Group BY

dt2

SSC-Addicted

Points: 474
More actions
June 24, 2016 at 5:56 am

#311298

Would anyone be able to answer why one query returns 1 NULL record and the other returns 0 records?
CREATE TABLE T1 (
ID INT NOT NULL);
SELECT MAX(ID)
FROM T1;
SELECT MAX(ID)
FROM T1
GROUP BY ();
Thanks
Darren

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Because one needs a group to show a row and the other doesn't.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

If you use the

SET SHOWPLAN_TEXT ON

you will get the following result

SELECT sum(ID) FROM T1

you will get the following plan

|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))

|--Stream Aggregate(DEFINE:([Expr1005]=Count(*), [Expr1006]=SUM([SST_DEV].[dbo].[T1].[ID])))

|--Table Scan(OBJECT:([SST_DEV].[dbo].[T1]))

SELECT sum(ID) FROM T1 GROUP BY ();

you will get the following plan

|--Stream Aggregate(DEFINE:([Expr1004]=SUM([SST_DEV].[dbo].[T1].[ID])))

|--Table Scan(OBJECT:([SST_DEV].[dbo].[T1]))

As the table is blank, you will never get the any result set as there is no group by value available. But 1st query does not need to because there is no group by mentioned.

hope it helps

dt2 SSC-Addicted Points: 474 More actions · Answer 3

Thanks for the replies. I also found this in a web article:

Scalar aggregates are queries with aggregate functions in the select list and no GROUP BY clause. Scalar aggregates always return a single row.

https://blogs.msdn.microsoft.com/craigfr/2006/09/06/aggregation/

Thanks

Darren