vedar vedar - 5 months ago 18
SQL Question

Conditional group on column attribute

I have a table with these columns:

pk
,
gender
,
attribute
and want to select top 10 attributes per gender. Currently I use two queries to accomplish that:

SELECT attribute, count(attribute)
FROM tab
WHERE gender=1
GROUP BY attribute
ORDER BY count(attribute) DESC
LIMIT 10


and another query for
WHERE gender=0
.

Can I make this to one query, and output both top 10 attributes per gender

male_attribute, count, female_attribute, count

Answer

I think that a Pivot Table solution would fit better your scenario. The response will be like:

 attribute     male     female
     A          10        12
     B           9         8 
     C           7         8
     D           6         5
     E           3         4

To get such result the SQL for PostgreSQL would be:

select attribute,
       sum(gender0) gender0,
       sum(gender1) gender1
  from (select attribute,
               case when gender=0 then 1 else 0 end as gender0,
               case when gender=1 then 1 else 0 end as gender1
          from tab) as tab
 group by attribute
 order by (case when sum(gender0)>sum(gender1) 
                then sum(gender0) 
                else sum(gender1) end) desc
 limit 10

Since you didn't specify which is male and female I left it as gender0 and gender1

Explaining the lines that I think it would look strange:

 order by (case when sum(gender0)>sum(gender1) 
                then sum(gender0) 
                else sum(gender1) end) desc   

This line will check which value is bigger the sum from gender0 or the sum from gender1 and will order by it desc. That way you will have the bigger value of the two first.

If you need any more explanation let me know.

See it working here (the values are different on working sample): http://sqlfiddle.com/#!15/c1432/1