CODEWITHSUNDEEP
mysqlcount
Mark H
Mark H

Reputation: 259

Create Percentage on Two Fields using Count on 3

Suppose I have a clothing inventory table with Type,Color,Size and I do a group on those three fields to get the count of each but then want to do get the percentage of each size for each Type and Color. I made a sql fiddle with the original count(*) for the three fields and wonder if I can then return the % for Size per each Type and Color.

http://sqlfiddle.com/#!9/c7a4d/4

For instance in the sql fiddle shown I get the following for Black Pants:

whereas I'd want

Upvotes: 1

Views: 36

Answers (2)

Mureinik
Mureinik

Reputation: 311988

You could cross join your query with a query selecting count(*) from the entire table, and divide the counts to get a percentage:

SELECT     Type, Color, Size, COUNT(*), COUNT(*) / total * 100 AS percentage
FROM       Clothing
CROSS JOIN (SELECT COUNT(*) AS total FROM Clothing) t
GROUP BY   Type, Color, Size

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270663

You can do aggregation and a join:

Select c.Type, c.Color, c.Size,
       Count(*), Count(*) / cc.cnt as proportion
from Clothing c join
     (select c.type, c.color, count(*) as cnt
      from clothing c
      group by c.type, c.color
     ) cc
     on cc.type = c.type and cc.color = c.color
group by c.Type, c.Color, c.Size, cc.cnt
having Count(*) > 1

It is unclear how the outer COUNT(*) condition fits into the total, so I left it out of the subquery.

Upvotes: 1

Related Questions