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
 Return unique count

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_UNIT
1 | 01
1 | 01
1 | 02
1 | 03
1 | 03
1 | 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]
Go to Top of Page

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

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

- Advertisement -