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
Go to Top of Page

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.
Go to Top of Page

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 :)
Go to Top of Page
   

- Advertisement -