CODEWITHSUNDEEP
sql
Scott
Scott

Reputation: 175

SQL % of data contains a value

I have a Microsoft SQL table with an organization column and an mi_status column, both of which are strings. I want to find out for each (grouped by) organization how many of the records have an mi_status of 'In Stock', how many total records there are, and then what % of the records are In Stock.

I can easily get the In Stock part via something like this:

SELECT organization, count(*)
FROM table
WHERE mi_status = 'In Stock'
GROUP BY organiztion

but I don't know how to also get the total rows in that same select since I've got the 'where' clause there.

Upvotes: 0

Views: 36

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

Use conditional aggregation:

SELECT organization, count(*) as total,
       SUM(CASE WHEN mi_status = 'In Stock' THEN 1 ELSE 0 END) as total_instock,
       AVG(CASE WHEN mi_status = 'In Stock' THEN 1.0 ELSE 0 END) as proportion_instock
FROM table
GROUP BY organization;

Upvotes: 3

Lamak
Lamak

Reputation: 70668

Use a CASE expression:

SELECT  organization, 
        SUM(CASE WHEN mi_status = 'In Stock' THEN 1 ELSE 0 END) In_Stock,
        COUNT(*) Total,
        SUM(CASE WHEN mi_status = 'In Stock' THEN 1 ELSE 0 END)*100.0/COUNT(*) Percentage
FROM dbo.YourTable
GROUP BY organization;

Upvotes: 2

pintxo
pintxo

Reputation: 2155

SELECT 
    organization, 
    count(*), 
    (SELECT COUNT(*) FROM table) as total
FROM 
    table
WHERE 
    mi_status = 'In Stock'
GROUP BY 
    organization

Upvotes: 0

Related Questions