Search code examples
mysqlgroup-bygroup-concat

Order By Lowest Value in total column, by year with GROUP BY on another column

My Table looks like the following:

wp_infraexchange_market_pivots table

Am using the following query to output this data in a table (front-end):

SELECT developer, GROUP_CONCAT(year) AS years, GROUP_CONCAT(total) AS totals, SUM(IFNULL(total, 0)) AS totals_sum
    FROM wp_infraexchange_market_pivots
    WHERE market_id = 2
    GROUP BY developer
    ORDER BY totals_sum DESC
    LIMIT 20;

This gives me output that looks like this:

query result

However, I need to output the same data, but the rows ordered by lowest total in a given year. So, if I want to get the results ordered from lowest total column in the year 2012, and order it in ASC order, how can I do this in a MySQL query? Is this even possible?

@scais - Have tried your answer, but am getting the following results:

enter image description here

This looks very close to what I need, cept I still need the GROUP_CONCAT to work.

Ok, so switched to a CASE statement in the SELECT and it almost acts properly...

SELECT developer, GROUP_CONCAT(year) AS years, GROUP_CONCAT(total) AS totals, SUM(IFNULL(total, 0)) AS totals_sum, 
CASE WHEN year = 2013 
    THEN total 
    ELSE NULL 
END AS year_total
        FROM wp_infraexchange_market_pivots
        WHERE market_id = 2
        GROUP BY developer
        ORDER BY year_total ASC
        LIMIT 20;

The above outputs the correct order and data for 2012 year, but if I change it to 2013 I don't get the correct order. Not sure why though. But the order is flawed and when I change it to 2015, 2016 also, it does not return the right order. Only returns the right order for the year 2012. I think what is happening here is that it returns right data only on first year it finds, than all other years, it returns NULL, and this is messing with the order of years that are greater than 2012 (the lowest year), but not sure how to fix this.

Here's a pic of what I'm trying to achieve with this query, where you can click the header of each column and have it ordered in ascending or descending order by year:

enter image description here

Solution

  • Thanks guys, but I figured it out this way and works like a charm:

    SELECT developer, GROUP_CONCAT(year) AS years, GROUP_CONCAT(total) AS totals, SUM(IFNULL(total, 0)) AS totals_sum, CASE WHEN year = 2016 THEN total ELSE NULL END AS year_total
    FROM wp_infraexchange_market_pivots
    WHERE market_id = 2
    GROUP BY developer
    ORDER BY MIN(year_total) ASC
    LIMIT 20;

    This sorts the results (keeping developers unique) in ASCENDING ORDER all total from lowest to highest in the year 2016. Now I can just simply remove 2016 and put in any year, and if I want to get highest to lowest, I just change ORDER BY MIN(year_total) ASC to ORDER BY MAX(year_total) DESC and all is PERFECT!

    Guess, a CASE statement was needed here! Thanks for all of your efforts guys!