Get sum of combinations - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Get sum of combinations

Author

Topic

carlrichter
Starting Member

2 Posts

Posted - 2013-11-07 : 12:11:14

I got this simple query

SELECT ID, NAME FROM Address

With the result like:

1, 'Home'
2, 'Home'
2, 'Invoice'
3, 'Home'
4, 'Home'
4, 'Decal'
4, 'Invoice'
5, 'Home'

For each ID I want the number of rows.

The result should be:

ID, Sum
1, 1
2, 2
3, 1
4, 3
5, 1

Another approach to the problem is to list all ID:s that have 'Home' and 'Invoice'. That should return "2" from the example above.

Is there anyone that can help me with this problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 13:13:19

number of rows just do like


SELECT ID,COUNT(*) AS [Sum]
FROM Table
GROUP BY ID

and to get ids having both Home and invoice use


SELECT ID
FROM Table
WHERE NAME IN ('Home','Invoice')
GROUP BY ID
HAVING COUNT(DISTINCT NAME)=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-07 : 14:02:29

==>Another approach to the problem is to list all ID:s that have 'Home' and 'Invoice'. That should return "2" from the example above.

Okay, you mean, exact no more no less, exactly
So try this:


SELECT ID
FROM [Address]
GROUP BY ID
HAVING COUNT(DISTINCT IIF(NAME IN ('Home','Invoice'), NAME, 'X')) = 2;

Why do I use IIF?
Easy, first for shorting and second for readability.

carlrichter
Starting Member

2 Posts

Posted - 2013-11-08 : 08:13:21

Thanks for your help guys. It was easy, but hard enough for me :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources