Is it possible to have multiple CASE in a GROUP BY that can make it so it doesn't group at all?

by UmbrellaCorpAgent   Last Updated September 11, 2019 13:26 PM

I'm trying to query come datas and on a spcific case I might have to group datas by multiple values. But most of the time it needs not to be grouped at all. So I'm using multiple CASE WHEN {...} inside the GROUP BY, and all the WHEN basically have the same condition. The problem is that if the condition is met, everything works fine. But if it's false, then the GROUP BY section is empty and the query returns only the first row.

I basically tried to reorganize the quesry in every way that came to my mind, nothing seemed to work, and I didn't find anything conclusive on internet.

I'm using MySql 5.7.

SELECT
    {element I want to select}
FROM
    {tables}
WHERE
    {conditions}
GROUP BY
    CASE WHEN (condition) THEN [table].[column] END,
    CASE WHEN (condition) THEN [table].[column] END,
    CASE WHEN (condition) THEN [table].[column] END
ORDER BY
    {...}

Full query :

SELECT
    tx.code,
    IFNULL(hr.label,'')                         AS rh_label,
    IFNULL(cli.label,'')                        AS client_label,
    DATE(FROM_UNIXTIME(created.value / 1000))   AS Created,
    IFNULL(item_enfant.label,'')                As Parasite,
    IFNULL(item_parent.label,'')                As Zone,
    CASE 
        WHEN :perWeek = 'week' THEN SUM(qte.value)
        ELSE qte.value
    END AS Quantite,
    CEILING(DATEDIFF(DATE(FROM_UNIXTIME(created.value / 1000)), DATE(FROM_UNIXTIME(:from / 1000))) / 7) AS Weeks
FROM tx
    LEFT JOIN tx_type           AS tt ON tt.id = tx.tx_type_id
    LEFT JOIN human_resource    AS hr ON hr.id = tx.human_resource_id
    LEFT JOIN client            AS cli ON cli.id = tx.client_id
    LEFT JOIN tx_state          AS ts ON ts.id = tx.current_tx_state_id
    LEFT JOIN workflow_step     AS ws ON ws.id = ts.workflow_step_id
    LEFT JOIN item              AS item_enfant ON item_enfant.item_list_id = tx.item_list_id
    JOIN item_type              AS ite ON ite.id = item_enfant.item_type_id
    LEFT JOIN item_meta         AS qte ON qte.item_id = item_enfant.id AND qte.name = 'qtePourRapport'
    LEFT JOIN item_prop         AS created ON created.item_id = item_enfant.id AND created.name = 'visite.timestamp'
    JOIN item                   AS item_parent ON item_parent.id = item_enfant.parent_item_id 
    JOIN item_type              AS itp ON itp.id = item_parent.item_type_id
WHERE
    ite.name = 'parasite' AND
    item_enfant.product_id IN (:parasiteIds) AND
    itp.name = 'zone' AND
    item_parent.product_id IN (:zoneIds) AND
    cli.id = (:clientId) AND 
    ws.logic_id = 600 AND 
    created.value BETWEEN :from AND :to AND 
    created.value IS NOT NULL AND qte.value IS NOT NULL
GROUP BY
    CASE WHEN :perWeek = 'week' THEN item_enfant.label END, #Parasite
    CASE WHEN :perWeek = 'week' THEN item_parent.label END, #Zone
    CASE WHEN :perWeek = 'week' THEN CEILING(DATEDIFF(DATE(FROM_UNIXTIME(created.value / 1000)), DATE(FROM_UNIXTIME(:from / 1000))) / 7) END #Weeks
ORDER BY 
    Created;

I'm getting the datas of the first row alone. And I actually have no idea how to get it just not to group if the condition is not met.

Tags : sql mysql-5.7


Answers 2


You need a unique value for the aggregation or two separate queries. The simplest method might be union all:

select . . .
from t
where <conditions not to group by>
union all
select . . .
from t
where <conditions to group by>
group by . . .;

You need to be sure that each subquery returns compatible columns.

Gordon Linoff
Gordon Linoff
September 11, 2019 13:13 PM

SELECT
    {element I want to select}
FROM
    {tables}
WHERE
    {conditions}
GROUP BY
    CASE WHEN (condition) THEN [table].[column] ELSE [some unique value of same data-type as column] END,
    CASE WHEN (condition) THEN [table].[column] ELSE [some unique value of same data-type as column] END,
    CASE WHEN (condition) THEN [table].[column] ELSE [some unique value of same data-type as column] END
ORDER BY
    {...}

I guess the missing ELSE clause will evaluate to NULL. This is constant, thus all rows will be in the same group, thus there will be only one row returned for this group. To avoid grouping you need unique values over all returned rows in every grouping-element.

Islingre
Islingre
September 11, 2019 13:18 PM

Related Questions