January 4, 2005 at 5:57 am
I have a script that counts the total number of records per unique client ID in a database table
SELECT
h.client,
count(*) as Count_Total_Slots
FROM database1.dbo.Hardware H
WHERE h.Removed IS NULL and (substring(h.model, 1, 4) = 'slot')
AND NOT (H.HardwareType = 6 AND H.SubType = 1 AND Model IS NULL )
and model is not null
group by h.client
having count(h.client) > 1
the script works fine. output is as follows
Client Count_Total_Slots
1 3
2 4
3 2
I now need an extra count column (Count_Free_Slots) that displays the count of those same records but whose value (h.Value)is empty.
any ideas
January 4, 2005 at 6:35 am
Hi,
Maybe something like this would do the trick:
SELECT
h.client,
count(*) as Count_Total_Slots,
b.EmptySlot
FROM database1.dbo.Hardware H
JOIN (SELECT COUNT(*) AS EmptySlot,client FROM database1.dbo.Hardware WHERE Removed IS NULL and (substring(model, 1, 4) = 'slot')
AND NOT (HardwareType = 6 AND SubType = 1 AND Model IS NULL )
and model is not null and value is null) b ON b.client = h.client
WHERE h.Removed IS NULL and (substring(h.model, 1, 4) = 'slot')
AND NOT (H.HardwareType = 6 AND H.SubType = 1 AND Model IS NULL )
and model is not null
group by h.client,b.EmptySlot
having count(h.client) > 1
January 4, 2005 at 6:56 am
sorry, doesn't work as it does not aggregate or count instances per client in the joined select statement, all it does is returns the total number of records with null values in it
thanks for your help though
January 4, 2005 at 6:57 am
What do you mean by 'empty' (null, zero?)
This will check for NULL
SELECT
h.client,
count(*) as Count_Total_Slots,
SUM(CASE WHEN h.Value IS NULL THEN 1 ELSE 0 END) as Count_Free_Slots
FROM database1.dbo.Hardware H
WHERE h.Removed IS NULL and (substring(h.model, 1, 4) = 'slot')
AND NOT (H.HardwareType = 6 AND H.SubType = 1 AND Model IS NULL )
and model is not null
group by h.client
having count(h.client) > 1
Far away is close at hand in the images of elsewhere.
Anon.
January 4, 2005 at 7:01 am
Sorry about that,
Just add the group by statment in the join like so:
SELECT
h.client,
count(*) as Count_Total_Slots,
b.EmptySlot
FROM database1.dbo.Hardware H
JOIN (SELECT COUNT(*) AS EmptySlot,client FROM database1.dbo.Hardware WHERE Removed IS NULL and (substring(model, 1, 4) = 'slot')
AND NOT (HardwareType = 6 AND SubType = 1 AND Model IS NULL )
and model is not null and value is null GROUP BY CLIENT ) b ON b.client = h.client
WHERE h.Removed IS NULL and (substring(h.model, 1, 4) = 'slot')
AND NOT (H.HardwareType = 6 AND H.SubType = 1 AND Model IS NULL )
and model is not null
group by h.client,b.EmptySlot
having count(h.client) > 1
January 4, 2005 at 7:06 am
works a treat, thanks for your help
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply