CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user342706
user342706

Reputation:

SQL Sum with Groupby

I'm looking to take a dataset like below and generate some statistics off the data. However, i'm having trouble figuring out how to get the data or if its even possible with a single query. I have different types of ports, in the below example its only user/printer/unknown, but there can be more than just those three. I also have status and again there can be more than just the statuses that are listed. I've tried using groupby, but it just doesn't seem to be the right tool since I'm wanting to group by one type, but I also need a count on each of the statuses?!? Any suggestions on how to achieve this would be greatly appreciated.

| Status        | Type 

| connected   | User
| disabled    | User
| connected   | Printer
| disabled    | Printer
| connected   | User
| disabled    | Unknown
| disabled    | Unknown


Want Resuls like this:

| Type      | Connected   | Disabled

| User      | 2           | 1
| Printer   | 1           | 1
| Unknown   | 0           | 2

Upvotes: 1

Views: 142

Answers (3)

Jeff Ogata
Jeff Ogata

Reputation: 57823

As @JNK mentioned, you can use PIVOT, but to do it dynamically, I believe you would have to construct the statement based on the available Status values.

The example below uses PIVOT with hard-coded status values, and then constructs the statement using the values from the sample data. You could also get the Status values from a table of valid statuses, etc.

create table #temp
(
    [Status] nvarchar(20),
    [Type] nvarchar(20)
)

insert into #temp values
    ('Connected', 'User'),
    ('Disabled', 'User'),
    ('Connected', 'Printer'),
    ('Disabled', 'Printer'),
    ('Connected', 'User'),
    ('Disabled', 'Unknown'),
    ('Disabled', 'Unknown')

-- pivot
select [Type], [Connected], [Disabled]
from 
    (select [Status], [Type] from #temp) t
    pivot
    (count([Status]) for [Status] in ([Connected], [Disabled])) as p    
order by [Connected] desc

-- dynamic pivot 
declare @statusList nvarchar(max),
        @pivot nvarchar(max)

-- get the list of Status values
select @statusList = coalesce(@statusList + ',', '') + '[' + [Status] + ']'
from (select distinct [Status] from #temp) t
order by [Status]

-- build the pivot statement
set @pivot = 
    'select [Type],' + @statusList + 
    ' from (select [Status], [Type] from #temp) t' +
    ' pivot (count([Status]) for [Status] in (' + @statusList + ')) p'

-- and execute it
exec (@pivot)

drop table #temp

Upvotes: 0

JNK
JNK

Reputation: 65217

Just use CASE and SUM.

SELECT Type,
       SUM(CASE WHEN Status = 'connected' then 1 else 0 END) as Connected,
       SUM(CASE WHEN Status = 'disabled' then 1 else 0 END) as disabled
From Table
GROUP BY Type

Upvotes: 3

Code Magician
Code Magician

Reputation: 24032

Hmmm...

Something like:

SELECT type, COUNT(CASE WHEN status = 'connected' then 1 else null END) as Connected, 
    COUNT(CASE WHEN status='disabled' then 1 else null END) as Disabled
FROM myTable  
GROUP BY type

Upvotes: 1

Related Questions