CODEWITHSUNDEEP
sqlsql-servert-sql
MHOOS
MHOOS

Reputation: 5306

Getting Status Count for an item with many statuses

Lets say I have the following table variable:

DECLARE @DevicesAndStatuses TABLE (Id BIGINT,[Status] INT);

DECLARE @myId BIGINT;
SET @myId = 1;

Inside above table I can have thousands of Ids(Id can be repeated) and statuses ranging between 1-50. What is the most efficient way of getting the count of all the statuses for a particular Id?

The traditional method which I have is as follows:

SELECT 
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 1) AS Status1,
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 2) AS Status2,
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 3) AS Status3,
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 4) AS Status4,
...
(SELECT COUNT(*) FROM @DevicesAndStatuses WHERE Id = @myId AND [Status] = 50) AS Status50,

FROM @DevicesAndStatuses WHERE Id = @myId

Are there potentially any better solution for getting the count of all the statuses [1-50] for a particular id?

Final result should be a single row containing 50 columns showing the count() of every status as Status1,Status2,...,Status50.*

Upvotes: 0

Views: 424

Answers (4)

Venkat
Venkat

Reputation: 109

Here you go

SELECT MAX(id) AS Id, status, COUNT(*)
  FROM @DevicesAndStatuses
 WHERE Id = @myId
GROUP BY status;

or

SELECT id AS Id, status, COUNT(*)
  FROM @DevicesAndStatuses
 WHERE Id = @myId
GROUP BY id,status;

Upvotes: 0

Alex
Alex

Reputation: 276

You'll need to use a Dynamic Pivot Query to achieve this:

I've done it using a generic example but poke me if you need a more specific version. You'll need to use a Temp Table instead of a Table Variable though.

The STUFF command is there to remove the , from the beginning of the strings.

CREATE TABLE #Items
(
    Item INT IDENTITY(1,1),
    [Status] INT
)

INSERT #Items
(Status)
VALUES
(1),(1),(1),(1),(1),(1),(1),(2),(2),(2),(2),(3),(3),(4),(4),(4),(4),(4),(4),(4),(4),(5);


DECLARE @StatusList NVARCHAR(MAX) = N'',
        @SumSelector NVARCHAR(MAX) = N''

SELECT @StatusList = CONCAT(@StatusList, N',', QUOTENAME(s.Status)),
       @SumSelector = CONCAT(@SumSelector, N',', N'SUM(', QUOTENAME(s.Status), N') AS Status_', s.Status)
        FROM (SELECT DISTINCT [Status] FROM #Items) AS s

SELECT @StatusList = STUFF(@StatusList, 1, 1, N''),
       @SumSelector = STUFF(@SumSelector, 1, 1, N'')

DECLARE @StatusPivotQuery NVARCHAR(MAX) = CONCAT(N'

    SELECT ', @SumSelector, N'
    FROM #Items AS s
    PIVOT
    (
        COUNT(s.[Status])
        FOR s.[Status] IN(', @StatusList, N')
    ) AS pvt ')

EXEC sys.sp_executesql @StatusPivotQuery

DROP TABLE #Items

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

My first suggestion is to use a group by:

SELECT status, count(*)
FROM @DevicesAndStatuses
WHERE Id = @myId
GROUP BY status;

The simplest way to get the information you want, but in multiple rows.

If you want multiple columns, then use conditional aggregation:

SELECT SUM(CASE WHEN [Status] = 1 THEN 1 ELSE 0 END) AS Status1,
       SUM(CASE WHEN [Status] = 2 THEN 1 ELSE 0 END) AS Status2,
       SUM(CASE WHEN [Status] = 3 THEN 1 ELSE 0 END) AS Status3,
       SUM(CASE WHEN [Status] = 4 THEN 1 ELSE 0 END) AS Status4,
       . . .
FROM @DevicesAndStatuses
WHERE Id = @myId

Upvotes: 2

marc_s
marc_s

Reputation: 754268

Sure:

SELECT Status, COUNT(*)
FROM @DevicesAndStatuses 
WHERE Id = @myId    
GROUP BY Status

This returns all Status values for Id = @myId, and their count - in one simple statement

Upvotes: 1

Related Questions