CODEWITHSUNDEEP
sqlcountdistinct
Hernando Salas
Hernando Salas

Reputation: 149

SQL count and distinct query

I'm trying to build up a query that would count and distinct at the same time. This is the raw data

Raw Data

+--------+---------+
| IntID  | ErrorID |
+--------+---------+
| ins001 |    1    |
| ins001 |    1    |
| ins001 |    2    |
| ins002 |    3    |
| ins002 |    5    |
| ins002 |    5    |
| ins003 |    4    |
| ins003 |    1    |
| ins003 |    1    |
+--------+---------+

What im trying to accomplish is a count for each error id for each distinct instrument id, as shown below:

What is expected

+--------+-------------+-------------+-------------+-------------+-------------+
| IntID  | ErrorID=001 | ErrorID=002 | ErrorID=003 | ErrorID=004 | ErrorID=005 |
+--------+-------------+-------------+-------------+-------------+-------------+
| ins001 |      2      |      1      |      0      |      0      |      0      |
| ins002 |      0      |      0      |      1      |      0      |      2      |
| ins003 |      2      |      0      |      0      |      1      |      0      |
+--------+-------------+-------------+-------------+-------------+-------------+

Any advice or help is greatly appreciated. Thanks in advance

Upvotes: 0

Views: 126

Answers (3)

Roberto
Roberto

Reputation: 533

This should work if you have a finite set of ErrorIDs that you know ahead of time.

    select IntID, [1] as [ErrorID=001], [2] as [ErrorID=002], [3] as [ErrorID=003], [4] as [ErrorID=004], [5] as [ErrorID=005]
    from
    (select IntID, ErrorID, 1 as cnt
        from #YourTable) as t
    pivot
    (
        count(cnt)
        for ErrorID in ([1], [2], [3], [4], [5])
    ) as pvt

Edit

As you requested, If you have a larger amount of Errors you can dynamically build the query. Coalesce helps to build the strings for any number of Errors. (RIGHT is only used to remove the first comma from the string)

    DECLARE @DisplayList    varchar(1000)
    DECLARE @SearchList     varchar(1000)
    DECLARE @sql            varchar(MAX)

    select @DisplayList = COALESCE(@DisplayList, '') + ',[' + cast(ErrorID as VARCHAR(100)) + '] as ' + ' [ErrorID=' + cast(ErrorID as VARCHAR(100)) + ']' from #YourErrorsTable
    select @SearchList = COALESCE(@SearchList, '') + ',[' + cast(ErrorID as VARCHAR(100)) + ']' from #YourErrorsTable

    set @sql = 'select IntID' + @DisplayList +'
        from
        (select IntID, ErrorID, 1 as cnt
            from #YourTable) as t
        pivot
        (
            count(cnt)
            for ErrorID in (' + RIGHT(@SearchList, LEN(@SearchList)-1) + ')
        ) as pvt'

    EXEC(@sql)

Upvotes: 2

SQLChao
SQLChao

Reputation: 7847

If it's a small amount of Error ID's you could do the following.

SQL Fiddle

SELECT 
    Instrumentid
  , SUM(CASE WHEN ErrorID = 1 THEN 1 ELSE 0 END) AS ErrorID_001
  , SUM(CASE WHEN ErrorID = 2 THEN 1 ELSE 0 END) AS ErrorID_002
  , SUM(CASE WHEN ErrorID = 3 THEN 1 ELSE 0 END) AS ErrorID_003
  , SUM(CASE WHEN ErrorID = 4 THEN 1 ELSE 0 END) AS ErrorID_004
  , SUM(CASE WHEN ErrorID = 5 THEN 1 ELSE 0 END) AS ErrorID_005
FROM YourTable
GROUP BY instrumentid

Upvotes: 0

mabe02
mabe02

Reputation: 2744

If you are using MySql or PostgreSQL, you could use a group by instead of distinct.

select InstrumentID, ErrorID, count (*)
from table
group by InstrumentID, ErrorID

Upvotes: 1

Related Questions