How to do an SQL request using CASE

Question

I'm trying to do an SQL request using a clause case, but always it return this error:

ERROR: syntax error at or near "CASE"

My request is the following:

SELECT distinct extract(month from "Facturation") as month, "LRU", "Client"

from "foundry_sync"."data"

    CASE month
  WHEN "month" =1 THEN 
    select avg("Montant_fac_eur") as c1 where "Facturation" between 1 and 6

 when "month" =2 THEN 
 select avg("Montant_fac_eur") as c2 where "Facturation" between 2 and 7

 when "month" =3 THEN
 select avg("Montant_fac_eur") as c3 where "Facturation" between 3 and 8

 when "month" = 4 then 
 select avg("Montant_fac_eur") as c4 where "Facturation" between 4 and 9

 when "month"=5 THEN
 select avg("Montant_fac_eur") as c5 where "Facturation" between 5 and 10

 when "month"=6 THEN 
 select avg("Montant_fac_eur") as c6 where "Facturation" between 6 and 11

 when "month"=7 THEN
 select avg("Montant_fac_eur") as c7 where "Facturation" between 7 and 11

 else ''

END 

group by "LRU", "Client", "Facturation"
order by "Client", "month"

I'm using MySQL. Facturation is a date.

Can someone please tell me me where's my fault ? Thank you.

This needs to happen before the `from` clause, not after it. — CBroe, Aug 09 '17 at 11:52
And neither your GROUP BY nor your ORDER BY clauses make any sense right now - because you used _text literals_ there, instead of actual _column names_. (Same for the CASEs as well.) — CBroe, Aug 09 '17 at 11:53
It makes no sense to use `SELECT DISTINCT` when you're using `GROUP BY`. The grouping already ensures that every row will be distinct. — Barmar, Aug 09 '17 at 11:57
There's no `FROM tablename` in the `SELECT avg()` subqueries. — Barmar, Aug 09 '17 at 11:58
All your double quotes are wrong, see https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Barmar, Aug 09 '17 at 12:00
And you mixed the two syntaxes of case expression with each other — Shadow, Aug 09 '17 at 12:01
@Shadow I don't mixed any thing I took the syntaxe from the official site of Mysql https://dev.mysql.com/doc/refman/5.7/en/case.html — vero, Aug 09 '17 at 12:05
It looks like `Facturation` is a `DATETIME` or `DATE`. What is `Facturation BETWEEN 1 AND 6` supposed to mean? — Barmar, Aug 09 '17 at 12:11
@vero yes, you are mixing up the 2 syntaxes of case, see Barmar's anser below. — Shadow, Aug 09 '17 at 15:06

score 2 · Accepted Answer · answered Aug 09 '17 at 12:15

There are so many things wrong, it's hard to know where to begin.

You're mixing up the two forms of CASE expression. One form is:

CASE <expression>
    WHEN <value> THEN <result>
    WHEN <value> THEN <result>
    ...
END

the other is:

CASE
    WHEN <condition> THEN <result>
    WHEN <condition> THEN <result>
    ...
END

You're trying to use a SELECT query as a value, but it's missing the FROM clause and you have to wrap a query in parentheses to use it as a value. I suspect you wanted this to be querying from the same table, in which case you shouldn't be doing a subquery, you should just use the aggregation function in the main query.

The CASE expression should be part of the SELECT list, not after the FROM clause.

If you want to create separate columns in the output for each case, they can't be in one CASE expression.

You have all your table and column names in double quotes, MySQL uses backticks to quote names.

You don't need SELECT DISTINCT when using GROUP BY.

You can't refer to an alias in the SELECT list in the same query, except in GROUP BY, ORDER BY, and HAVING.

It should be:

SELECT MONTH(Facturation) AS month, LRU, Client,
    AVG(CASE WHEN MONTH(Factuation) = 1 AND Facturation BETWEEN 1 AND 6
        THEN Montant_fac_eur END) AS c1,
    AVG(CASE WHEN MONTH(Factuation) = 2 AND Facturation BETWEEN 2 AND 7
        THEN Montant_fac_eur END) AS c2,
    AVG(CASE WHEN MONTH(Factuation) = 3 AND Facturation BETWEEN 3 AND 8
        THEN Montant_fac_eur END) AS c3,
    AVG(CASE WHEN MONTH(Factuation) = 4 AND Facturation BETWEEN 4 AND 9
        THEN Montant_fac_eur END) AS c4,
    ...
FROM foundry_sync.data
GROUP BY `LRU`, `Client`, `Facturation`
ORDER BY Client, month

score 1 · Answer 2 · answered Aug 09 '17 at 12:00

You must set the CASE before the FROM. I have taken off all the ", and you need brakets for every select statement after the where's, I would take off the GROUP BY and the ORDER BY clause too. And as someone said, you need a FROM clause in every select

SELECT distinct extract(month from Facturation) as month, LRU, Client,
    CASE month
  WHEN month =1 THEN 
    (select avg(Montant_fac_eur) as c1 where Facturation between 1 and 6)

 when month =2 THEN 
    (select avg(Montant_fac_eur) as c2 where Facturation between 2 and 7)

 when month =3 THEN
    (select avg(Montant_fac_eur) as c3 where Facturation between 3 and 8)

 when month = 4 then 
    (select avg(Montant_fac_eur) as c4 where Facturation between 4 and 9)

 when month=5 THEN
    (select avg(Montant_fac_eur) as c5 where Facturation between 5 and 10)

 when month=6 THEN 
    (select avg(Montant_fac_eur) as c6 where Facturation between 6 and 11)

 when month=7 THEN
    (select avg(Montant_fac_eur) as c7 where Facturation between 7 and 11)

 else ''

END 

from foundry_sync.data
group by LRU, Client, Facturation
order by Client, month

How to do an SQL request using CASE

2 Answers2

Linked