The basic Aggregate function

Question

The basic Aggregate function

Shiva N

Hall of Fame

Points: 3125
More actions
July 23, 2016 at 10:41 pm

#315586

Comments posted to this topic are about the item The basic Aggregate function
Thanks,
Shiva N
Database Consultant
July 24, 2016 at 10:52 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Nice straightforward question.

This is one of those things (like the handling of SUM) the makes me think that someone had a screw loose when they were specifying how aggregates worked in unusual circumetances.

Tom

david.gugg SSCertifiable Points: 6101 More actions · Answer 2

I think this kind of makes sense if you come at it from the Oracle perspective. In Oracle, to select values you have to go against the SYS.DUAL table, a table with a single dummy record. For example, the equivalent of SELECT GETDATE(); in Oracle would be SELECT sysdate FROM dual;. So the equivalent of SELECT COUNT(1); in Oracle would be SELECT COUNT(1) FROM dual;, which would return 1.

Be still, and know that I am God - Psalm 46:10

webrunner SSC-Dedicated Points: 31882 More actions · Answer 3

Thanks, great question. The behavior is not at all what I expected, but it is good to know.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

sknox SSChampion Points: 12388 More actions · Answer 4

david.gugg (7/25/2016)
I think this kind of makes sense if you come at it from the Oracle perspective. In Oracle, to select values you have to go against the SYS.DUAL table, a table with a single dummy record. For example, the equivalent of SELECT GETDATE(); in Oracle would be SELECT sysdate FROM dual;. So the equivalent of SELECT COUNT(1); in Oracle would be SELECT COUNT(1) FROM dual;, which would return 1.

So what happens in Oracle if you just execute SELECT GETDATE(); ?

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

david.gugg (7/25/2016)
I think this kind of makes sense if you come at it from the Oracle perspective. In Oracle, to select values you have to go against the SYS.DUAL table, a table with a single dummy record. For example, the equivalent of SELECT GETDATE(); in Oracle would be SELECT sysdate FROM dual;. So the equivalent of SELECT COUNT(1); in Oracle would be SELECT COUNT(1) FROM dual;, which would return 1.

Didn't know that about Oracle.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

Easy one, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP