Search code examples
sqlgroup-bycube

How Do I Use Case When with Group By statement

I have made a query but it don't give me what i want :

SELECT     Designation_projet, COUNT(*) AS [Nb Demande], CASE WHEN Validation = 0 THEN COUNT(*) END AS Validée, CASE WHEN Validation = 1 THEN COUNT(*) 
        END AS NonValidée, CASE WHEN Commandé = 0 THEN COUNT(*) END AS NonCommandé, 
       CASE WHEN Commandé <> 0 THEN COUNT(*) END AS Commandé,  SUM(TotalHT) AS TotalHT
FROM         V_DemandeAchat
GROUP BY Designation_projet,Commandé,Validation

Please help me to achieve my gool. enter image description here

Thanks in advance

Solution

  • You can do this using condition aggregation. That means that the CASE expression is the argument to SUM() -- it goes "inside" not "outside":

    SELECT Designation_projet, COUNT(*) AS [Nb Demande],
       SUM(CASE WHEN Validation = 0 THEN 1 ELSE 0 END) as Validée,
       SUM(CASE WHEN Validation = 1 THEN 1 ELSE 0 END) as NonValidée, 
       SUM(CASE WHEN Commandé = 0 THEN 1 ELSE 0 END) AS NonCommandé, 
       SUM(CASE WHEN Commandé <> 0 THEN 1 ELSE 0 END) END AS Commandé,
       SUM(TotalHT) AS TotalHT
FROM V_DemandeAchat
GROUP BY Designation_projet;

    If you want one row per Designation_projet, then that should be the only key in the GROUP BY.

    Assuming Validation takes on only two values, you can simplify the first two aggregation expressions:

    SELECT Designation_projet, COUNT(*) AS [Nb Demande],
       SUM(1 - Validation) as Validée,
       SUM(Validation = 1) as NonValidée, 
       SUM(CASE WHEN Commandé = 0 THEN 1 ELSE 0 END) AS NonCommandé, 
       SUM(CASE WHEN Commandé <> 0 THEN 1 ELSE 0 END) END AS Commandé,
       SUM(TotalHT) AS TotalHT
FROM V_DemandeAchat
GROUP BY Designation_projet;

    You may be able to simplify the Commandé expressions as well.