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.
Author |
Topic |
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-09-06 : 16:07:16
|
I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to. ID | ID_UNIT1 | 01 1 | 01 1 | 02 1 | 03 1 | 031 | 04 1 | 04 I need two results. Total Units = 7 - easy to do by using count()Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want. |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-09-06 : 16:13:56
|
[code]DECLARE @Foo TABLE (ID INT, ID_UNIT INT)INSERT @Foo VALUES(1, 01), (1, 01), (1, 02), (1, 03), (1, 03),(1, 04), (1, 04)SELECT COUNT(*) AS TotalUnits ,COUNT(DISTINCT ID_UNIT)FROM @Foo[/code] |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-06 : 16:14:48
|
[code]SELECT COUNT(ID_UNIT), COUNT(DISTINCT ID_UNIT) FROM YourTable[/code] |
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-09-06 : 16:23:42
|
Wow. I did not know you can put distinct in count function. I will try it on Monday. Many thanks and have a great weekend!!!! |
|
|
|
|
|
|
|