CODEWITHSUNDEEP
sqlmysql
Pentium10
Pentium10

Reputation: 208012

order by count when you have to count only certain items

I have this query:

SELECT tips.* 
FROM   `tips` `t` 
       LEFT JOIN tip_usage 
         ON tip_usage.tip_id = t.id 
GROUP  BY t.id 
ORDER  BY COUNT(CASE 
                  WHEN status = 'Active' THEN status 
                  ELSE NULL 
                END) DESC

As you see here, I use a left join and I count only the records which are Active

Can I make this query to be different, and to lose the case stuff from the count?

Upvotes: 0

Views: 189

Answers (3)

Dean Rather
Dean Rather

Reputation: 32394

SELECT tips.*, COUNT(*) AS number
FROM tip_usage
LEFT JOIN tips ON tips.id = tip_id
WHERE STATUS = "Active"
GROUP BY tip_id
ORDER BY number DESC

Upvotes: -1

Ken Downs
Ken Downs

Reputation: 4827

Quick note, you cannot select t.* and group on t.id. So with that being said:

SELECT t.id,coalesce(tu.cntUsed,0) as cntUsed
  FROM   `tips` `t` 
  LEFT 
  JOIN (Select tip_id,count(*) as cntUsed
          from tip_usage
         WHERE status='Active'
         group by tip_id
       ) tu
    ON t.id = tu.tip_id
 ORDER coalesce(tu.cntUsed,0)

Since you want to left-join and include the tips that have no usage, this at least sorts them all at the top with a value of zero, which is the most accurate statement of the reality of what is in the tables.

Upvotes: 1

Yaakov Ellis
Yaakov Ellis

Reputation: 41550

If you want to return all tips, regardless of status, and but sort by number of Active records, then this is as pretty as you are going to get.

If you only want to return active tips, then you can add Where status = 'Active' and then just order by Count(t.id) desc.

One alternative is that you have a NumActive int column in the tips table, and you keep this update whenever a new tip_usage record is added or modified for a given tip. This puts more overhead into the insert/delete/update operations for tip_usage, but would make this query much simpler:

select *
from tips
Order by tips.NumActive desc

Another alternative is:

Select tips.*
From tips
Order by (
  Select count(tip_id) 
  From tips_usage as t 
  Where t.tip_id = tips.id and status = 'Active') DESC

Though this exchanges a case for a subquery, so just complex in a different way.

Upvotes: 2

Related Questions