CODEWITHSUNDEEP
sqlcount
null
null

Reputation: 131

Two ways to use Count, are they equivalent?

Is

SELECT COUNT(a.attr)
FROM TABLE a

equivalent to

SELECT B
FROM (SELECT COUNT(a.attr) as B
      FROM TABLE a)

I would guess no, but I'm not sure.

I'm also assuming the answer would be the same for functions like min, max, avg, correct?

EDIT:

This is all out of curiosity, I'm still new at this. Is there a difference between the value returned for the count of the following and the above?

SELECT B, C
FROM (SELECT COUNT(a.attr) as B, a.C
      FROM TABLE a
      GROUP BY c)

EDIT AGAIN: I looked into it, lesson learned: I should be awake when I try to learn about these things.

Upvotes: 2

Views: 167

Answers (5)

Gage
Gage

Reputation: 7513

Yes there are. All your doing in the second one is naming the returned count B. They will return the same results.

http://www.roseindia.net/sql/sql-as-keyword.shtml

EDIT: Better example: http://www.w3schools.com/sql/sql_alias.asp

The third example will be different because it contains a group by. It will return the count for every distinct a.C entry. Example

B          C
w/e        a
w/e        a
w/e        b
w/e        a
w/e        c

Would return 

3       a
1       b
1       c

Not necessarily in that order

Easiest way to check all of this is to try it for yourself and see what it returns.

Upvotes: 1

Tony
Tony

Reputation: 10327

Am posting this answer to supplement what has already been said in the other answers, and because you cannot format comments :)

You can always check the execution plan to see if queries are equivalent; this is what SQL Server makes of it:

DECLARE @A TABLE
(
  attr int,
  c int
)

INSERT @A(attr,c) VALUES(1,1)
INSERT @A(attr,c) VALUES(2,1)
INSERT @A(attr,c) VALUES(3,1)
INSERT @A(attr,c) VALUES(4,2)
INSERT @A(attr,c) VALUES(5,2)

SELECT count(attr) FROM @A

SELECT B
FROM (SELECT COUNT(attr) as B
      FROM @A) AS T

SELECT B, C
FROM (SELECT COUNT(attr) as B, c AS C
      FROM @A
      GROUP BY c) AS T

Here's the execution plan of the SELECT statments, as you can see there is no difference in the first two:

enter image description here

Upvotes: 1

JNK
JNK

Reputation: 65177

The results are the same, and would be the same as:

SELECT E
FROM
(SELECT D as E
    FROM
    (SELECT C as D
        FROM
        (SELECT B as C
            FROM 
            (SELECT COUNT(a.attr) as B
                FROM TABLE a))))

And equally as pointless.

The second query is essentially obfuscating a COUNT and should be avoided.

EDIT:

Yes, your edited query that was added to the OP is the same thing. It's just adding a subquery for no reason.

Upvotes: 2

digor_ua
digor_ua

Reputation: 592

Your first code sample is correct, but second does not have any sense. You just select all data twice without any operations. So, output for first and second samples will be equal.

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

Technically, they are not the same, the first one is a simple select, the second one is a select with a sub select.
But every sane optimizer will generate the same execution plan for both of them.

Upvotes: 6

Related Questions