CODEWITHSUNDEEP
mysqlgroup-byaggregates
Kylie
Kylie

Reputation: 11749

Getting a seperate/minimum value of another column, of an aggregate in MYSQL

I have this query, which retrieves aggregates of all my leads based on leadtype, then groups them into cities.

    SELECT city, assign_count, 
    SUM(leadtype = 'paper' AND status='NEW' ) paper,
    SUM(leadtype = 'door' AND status='NEW' ) door
    FROM leads GROUP BY city

This is all fine and good and I can display the results as follows

   City 1 :  Door | 12   Paper | 23
   City 2 :  Door | 33   Paper | 44
   City 3 :  Door | 44   Paper | 12
   Etc etc etc....

However, I now want to highlight the ones with the lowest assign_count values.

How can I get the lowest assign_count per paper and door. I am retrieving it, but it is just the assign_count of the last selected lead obviously, and only for that city as a whole. Not for the seperate grouped by aggregates.

I want to display like this.

  City 1 : Door |Cnt: 1| 12  Paper |Cnt: 2| 23  // Door would be highlighted green in this case
  City 2 : Door |Cnt: 6| 12  Paper |Cnt: 4| 23  // Paper would be highlighted green in this case
  etc etc

However, with the above query I can only display like this,

 City 1 : Cnt : 0  Door |12  Paper | 23
 City 2 : Cnt : 7  Door |12  Paper | 55

and even the count is wrong, because its clearly just selecting the assign_count value of the last lead in the query, not necessarily the lowest assign_count, and definitely not the lowest for each door and paper

Hopefully I made myself clear enough here.

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Is this what you want?

SELECT city, assign_count, 
       SUM(leadtype = 'paper' AND status = 'NEW' ) as paper,
       MIN(case when eadtype = 'paper' AND status = 'NEW' then assign_count end) as ac_paper,
       SUM(leadtype = 'door' AND status = 'NEW' ) as door,
       MIN(case when eadtype = 'door' AND status = 'NEW' then assign_count end) as ac_door
FROM leads
GROUP BY city;

As a note: you shouldn't include assign_count in the SELECT list. It should either be in an aggregation function 'MIN()' or in the GROUP BY clause. The value you get is indeterminate (according to the MySQL documentation).

Upvotes: 1

Related Questions