CODEWITHSUNDEEP
mysql
Junior75
Junior75

Reputation: 1

I'm getting same values on this query using CASE WHEN

i'm very new on mysql and i'm trying to get some values on this query using the CASE WHEN validation

SELECT u.id, u.username as "Nombre", 
       tm.name as "Facturable", 
  CASE tm.name      
  WHEN tm.name = "facturable_actividad" and tm.value= 1 then SUM(TIMESTAMPDIFF(MINUTE,t.start_time, t.end_time)) END AS "HH",
       tm.name as "No facturable",
  CASE tm.name
  WHEN "facturable_actividad" AND tm.value= 0 THEN SUM(TIMESTAMPDIFF(MINUTE,t.start_time, t.end_time)) END as "NO HH"
FROM  kimai2_users u
JOIN kimai2_timesheet t
ON u.id = t.user
JOIN kimai2_timesheet_meta tm
ON t.id = tm.timesheet_id
GROUP BY t.user;

Basically i'm saying that i want to create a new row called "HH" and inside it i say if "facturable_actividad" is 1 then do a sum a get X result. Else i have other row "NO HH" where i do the same but "facturable_actividad" is 0.

I get this:

query result

As you can see i get the same values for HH and NO HH (hh means billable), the NO HH should be much less than HH and not the same !

Upvotes: 0

Views: 28

Answers (1)

EdmCoff
EdmCoff

Reputation: 3576

I think the short answer to you question is that when you write

CASE tm.name      
WHEN tm.name = "facturable_actividad" and tm.value= 1

you are effectively checking whether tm.name = (tm.name = "facturable_actividad" and tm.value= 1) which is different than checking only the part in brackets.

If you are checking a single column for certain values, it makes sense to write something like

CASE mycolumn
 WHEN "a" then 0
 WHEN "b" then 1
 ELSE 2
END

This checks whether mycolumn="a" then whether mycolumn="b" as you would expect.

But if you want to do multiple conditions as part of a WHEN, you probably don't want to write anything after the CASE part.

CASE
 WHEN mycolumn="a" and mycolumn2="b" then 1
 WHEN mycolumn="c" and mycolumn2="d" then 2
 ELSE 3
END

As you would expect, this checks the first condition (mycolumn="a" and mycolumn2="b") then the second condition (mycolumn="c" and mycolumn2="d")

Since you are checking multiple columns in the same WHEN, you probably don't want to write tm.name after the case. So instead of CASE tm.name WHEN [condition] you simply write CASE WHEN [condition]. The entire condition is spelled out the WHEN part.

SELECT u.id, u.username as "Nombre", 
       tm.name as "Facturable", 
  CASE      
  WHEN tm.name = "facturable_actividad" and tm.value= 1 then SUM(TIMESTAMPDIFF(MINUTE,t.start_time, t.end_time)) END AS "HH",
       tm.name as "No facturable",
  CASE
  WHEN tm.name = "facturable_actividad" AND tm.value= 0 THEN SUM(TIMESTAMPDIFF(MINUTE,t.start_time, t.end_time)) END as "NO HH"
FROM  kimai2_users u
JOIN kimai2_timesheet t
ON u.id = t.user
JOIN kimai2_timesheet_meta tm
ON t.id = tm.timesheet_id
GROUP BY t.user;

Although I think the above answers your question, I suspect what you are really going for is:

 SELECT u.id, u.username as "Nombre", 
       tm.name as "Facturable", 
  SUM(CASE     
  WHEN tm.name = "facturable_actividad" and tm.value= 1 then TIMESTAMPDIFF(MINUTE,t.start_time, t.end_time) END) AS "HH",
       tm.name as "No facturable",
  SUM(CASE 
  WHEN tm.name = "facturable_actividad" AND tm.value= 0 THEN TIMESTAMPDIFF(MINUTE,t.start_time, t.end_time) END) as "NO HH"
FROM  kimai2_users u
JOIN kimai2_timesheet t
ON u.id = t.user
JOIN kimai2_timesheet_meta tm
ON t.id = tm.timesheet_id
GROUP BY t.user;

Here, the SUM wraps the CASE statement instead of the other way around. I am making an assumption about what you're trying to do here.

Upvotes: 1

Related Questions