0

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.

Kylie
  • 11,421
  • 11
  • 47
  • 78
  • 1
    It is not clear to me what you are asking. Why can't you just do this in the application. If you can, fix your question so the tables are structured as tables, with column headers. I have no idea what this line is supposed to mean (for instance): `City 1 : Door |Cnt: 1| 12 Paper |Cnt: 2| 23`. – Gordon Linoff Nov 06 '14 at 01:52
  • I just want the assign_count value, grouped by the aggregates, as opposed to the city. – Kylie Nov 06 '14 at 01:55
  • What is `assign_count`? How does your query can give a result set with `City 1 : Door | 12 Paper | 23`...? – ForguesR Nov 06 '14 at 01:59
  • It SUM's door and paper. And groups by city. I dont get why thats so hard to understand. The query is pretty straightforward. Its the assign_count per door and paper that Im asking about – Kylie Nov 06 '14 at 01:59

1 Answers1

1

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).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786