sum a count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum a count

Author

Topic

ka365
Starting Member

3 Posts

Posted - 2012-02-13 : 17:55:29

I've read all of the "sum a count" posts and haven't found a clear answer to my question.

I am making a (distinct) count of column of ID values called EO_ID, and then i need to sum the resulting count.

My code:
SELECT TRACTNAME, CNAME, COUNT(DISTINCT EO_ID) AS AMT
FROM AU_X_NDDB
GROUP BY TRACTNAME, CNAME

E.g. of my results:
TRACTNAME, CNAME, AMT
Smith, Hall's tarplant, 1
Smith, Hall's tarplant, 1
Smith, Hall's tarplant, 1
Smith, Piute cypress, 2
Smith, praire falcon, 2

There are 3 Hall's tarplants but there are several duplicates, so first I get the DISTINCT COUNT of their EO_ID, but then I need to sum that count.

Any thoughts on how I can get a single row in my output for Hall's tarplant with a total amount of 3?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 18:10:35

[code]
SELECT TRACTNAME, CNAME,SUM(AMT) AS SUMAMT
FROM
(
SELECT TRACTNAME, CNAME, COUNT(DISTINCT EO_ID) AS AMT
FROM AU_X_NDDB
GROUP BY TRACTNAME, CNAME
)t
GROUP BY TRACTNAME, CNAME
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ka365
Starting Member

3 Posts

Posted - 2012-02-13 : 19:04:46

Thank you!

I saw that in other posts but when I tried it I neglected to put the little "t" at the end - I appreciate your help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 19:13:47

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-02-14 : 02:45:28

quote:
Originally posted by ka365

Thank you!

I saw that in other posts but when I tried it I neglected to put the little "t" at the end - I appreciate your help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/quote]
That t is a alias for a derived table

Madhivanan

Failing to plan is Planning to fail

ka365
Starting Member

3 Posts

Posted - 2012-02-14 : 12:51:44

thanks madhivanan. after visakh16's post, i tried searching for more information on the little t, but didn't know what it was called - derived table!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:46:39

quote:
Originally posted by ka365

thanks madhivanan. after visakh16's post, i tried searching for more information on the little t, but didn't know what it was called - derived table!

yep..As you're deriving a table out of inner query and for parser to recognise it you need to give it a name which is alias t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources