?
Solved

Want to count unique rows and total rows in SELECT query

Posted on 2005-05-13
10
Medium Priority
?
468 Views
Last Modified: 2010-05-19
I have a table that has duplicate rows.
The table has about a dozen fields.
I want to select the COUNT of rows and the COUNT of unique rows grouped by Field1,DateField.

I thought this would be easy, but I'm getting nowhere.

Why can't I just do a Count(Distinct *)?

Any ideas?

Thanks.
0
Comment
Question by:jholton
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 13996979
SELECT     COUNT(fld1) AS Expr1
FROM         dbo.tablname

SELECT     COUNT(fld1) AS Expr1, fld1
FROM         dbo.tablename
GROUP BY fld1
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13997001
SELECT COUNT(*) FROM (
SELECT DISTINCT * FROM YourTable) A

SELECT Field1, DateField, COUNT(*) FROM (
SELECT DISTINCT * FROM YourTable) A
GROUP BY Field1, DateField
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13997101
If you want the overall count and the distinct count of a *single* column, you can do that in one query:


SELECT COUNT(*), COUNT(DISTINCT field1)
FROM table1


But, as you've discovered, COUNT(DISTINCT ...) only works with a single column/expression.  If you want to use more than one column, you need to use a subquery (or two separate queries):

SELECT COUNT(*),
    (SELECT COUNT(*)
     FROM (
         SELECT DISTINCT field1, dateField
         FROM table1
         ) AS t1)
FROM table1
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:jholton
ID: 13997554
I'm still not getting it.
I don't think I was specific enough.
My table looks like this:
EntryDate
EntryType
Field1
Field2
Field3
Field4
etc

I want to see the number of rows and the number of unique rows for a given EntryDate and EntryType.
So, my results should look something like this:

EntryDate                      EntryType  Total       Unique
2005-05-06 00:00:00.000           DI           1607      501
2005-05-06 00:00:00.000           DW         1013            854
2005-05-06 00:00:00.000           IN           1683      1258
2005-05-06 00:00:00.000           NP          6311       5158
2005-05-06 00:00:00.000           RS          313              125
2005-05-06 00:00:00.000           UP           1940       1000
2005-05-07 00:00:00.000           DI            570      325
2005-05-07 00:00:00.000           DW          383             265
2005-05-07 00:00:00.000           IN            876      666
2005-05-07 00:00:00.000           NP           950      752
2005-05-07 00:00:00.000           RS           126      104
2005-05-07 00:00:00.000           UP           979      845


Thanks
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 500 total points
ID: 13997589
Try this:

SELECT A.Entrydate, A.EntryType, A.Total, B.UniqueCount
FROM (
SELECT EntryDate, EntryType, COUNT(*) AS Total
FROM YourTable
GROUP BY EntryDate, EntryType) A
INNER JOIN
(SELECT EntryDate, EntryType, COUNT(*) AS UniqueCount
FROM
(SELECT DISTINCT EntryDate, EntryType
FROM YourTable) DistinctTable
GROUP BY EntryDate, EntryType) B
ON A.EntryDate = B.EntryDate AND A.EntryType = B.EntryType
0
 

Author Comment

by:jholton
ID: 13998310
That's it.  
Thanks.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13998446
Doesn't this:


INNER JOIN
(SELECT EntryDate, EntryType, COUNT(*) AS UniqueCount
FROM
(SELECT DISTINCT EntryDate, EntryType
FROM YourTable) DistinctTable
GROUP BY EntryDate, EntryType) B


always return a UniqueCount of 1?
0
 

Author Comment

by:jholton
ID: 14013134
Yes it did.
I had to add more (primary key) fields to the sub-sub- query to make it return unique counts.  

SELECT A.Entrydate, A.EntryType, A.Total, B.UniqueCount
FROM (
SELECT EntryDate, EntryType, COUNT(*) AS Total
FROM YourTable
GROUP BY EntryDate, EntryType) A
INNER JOIN
(SELECT EntryDate, EntryType, COUNT(*) AS UniqueCount
FROM
(SELECT DISTINCT EntryDate, EntryType,Field1, Field2, etc
FROM YourTable) DistinctTable
GROUP BY EntryDate, EntryType) B
ON A.EntryDate = B.EntryDate AND A.EntryType = B.EntryType
0
 

Author Comment

by:jholton
ID: 14013139
<b>test</b>.
How can you format text here?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 14013159
You can't, its text only.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question