CODEWITHSUNDEEP
sql-server
shishir
shishir

Reputation: 851

Total Count of column value in Sql Server

Two tables, TableA and TableB with column "filename" which has same value in both table.

only the number of occurance of data is different.

e.g

|###TableA#########| 

|id|filename_TableA|
|01|file1          |
|02|file1          |
|03|file2          |
|04|file2          |
|05|file3          |
|06|file4          |

|## TableB ########|

|id|filename_TableB|
|01|file1          |
|02|file1          |
|03|file1          |
|04|file2          |
|05|file2          |
|06|file3          |
|07|file3          |
|08|file4          |
|09|file4          |

I need to generate a SQL query which shows the distinct filename with there

number of count and totalcount of the distinct filename.

like this: enter image description here

using select count(distinct filename_TableA) as totalCount from TableA gives the totalCount of filename but I am not able to generate the sql query for above result output.

Tried for single table:

select 
filename_TableA,
count(filename_TableA)as filecount_TableA,
totalCount = (
                select count(distinct filename_TableA) from TableA
             )
from TableA
group by filename_TableA

Upvotes: 2

Views: 2259

Answers (1)

Alex
Alex

Reputation: 5157

Always try to break your problem into smaller parts!

Your question consists of two parts:

  1. Get distinct files and counts from tableA
  2. Get distinct files and counts from tableB

We write queries:

1.

SELECT filename_TableA, COUNT( * ) AS filecount_TableA
FROM TableA
GROUP BY filename_TableA

2.

SELECT filename_TableB, COUNT( * ) AS filecount_TableB
FROM TableB
GROUP BY filename_TableB

Check that the results of each individual query are correct.

Then we combine the queries:

SELECT filename_TableA, filename_TableB, filecount_TableA, filecount_TableB, ISNULL( filecount_TableA, 0 ) + ISNULL( filecount_TableB, 0 ) AS totalCount,
    COUNT(*) OVER() AS UniqueFileCount
FROM
        ( SELECT filename_TableA, COUNT( * ) AS filecount_TableA
        FROM TableA
        GROUP BY filename_TableA ) AS A
    FULL OUTER JOIN
            ( SELECT filename_TableB, COUNT( * ) AS filecount_TableB
            FROM TableB
            GROUP BY filename_TableB ) AS B
        ON A.filename_TableA = filename_TableB

Note: To cover scenarios where a file name may appear in one table but not the other I have used FULL OUTER JOIN. If you do not have such a scenario i.e. each file name will appear at least once in every table, then you should use INNER JOIN as it will be faster.

Upvotes: 1

Related Questions