SQL: Using CASE over TABLE that has been GROUPED

I am working with an SQLite Database that has a table CarUse with three fields CarID (VARCHAR(20)), tDate (VARCHAR(20), and Kms (INT).

The goal is to create a table that will summarise the Kms over a date range (D1 to D2) and then label them as ‘High Kms’ if they are over X and as ‘Low Kms’ if they are under Y.

So, constructing this step by step, I can easily find the Total Kms for each Car in the CarUse table using the GROUP BY command:

SELECT CarID, SUM(Kms)
FROM CarUse
WHERE tDate > D1 AND tDate < D2
GROUP BY CarID

Now, essentially what I want to do is to create another field to add to the resultant data that classifies the SUM(Kms) so that if SUM(Kms) > X it’s ‘High Kms’ and if < Y it’s ‘Low Kms’.

To begin with I tried:

SELECT CarID, SUM(Kms) AS A, 
   CASE A 
      WHEN A > X THEN 'High Kms' 
      WHEN A < Y THEN 'Low Kms'
   END 
FROM CarUse
WHERE tDate > D1 AND tDate < D2
GROUP BY CarID

When I run this I get an error saying that ‘no such column: A’. My first thought was that the alias ‘A’ must be assigned after the result is created. As a quick fix, I hoped that if instead of using an alias A, I instead just reference the SUM(Kms) i.e.

SELECT CarID, SUM(Kms), 
   CASE SUM(Kms) 
      WHEN  SUM(Kms) > X THEN 'High Kms' 
      WHEN  SUM(Kms) < Y THEN 'Low Kms' 
   END
FROM CarUse
WHERE tDate > D1 AND tDate < D2
GROUP BY CarID

This executes but does not perform the desired result. In fact, it only assigns ‘High Kms’ to those that have SUM(Kms) = 0. Again here I’m assuming that this is due to the fact that Sum(Kms) is not known when it’s running.

I decided to try a different tactic and create the ‘SUM table’ separately and then run the same query over it, i.e.

SELECT A.CarID, A.TotalDist, 
   CASE A.TotalDist 
      WHEN A.TotalDist > X THEN 'High Kms' 
      WHEN A.TotalDist < Y THEN 'Low Kms'
   END 
FROM (
   SELECT CarID, SUM(Kms) AS TotalDist
   FROM CarUse
   WHERE tDate > D1 AND tDate < D2
   GROUP BY CarID
) AS A

This executes but sadly doesn’t do the desired action. It only assigns ‘High Kms’ to those where A.TotalDist = 0.

Can someone help me understand exactly what is going on in terms of compilation to functionality? and whether this is possible

Any advice would be greatly appreciated.

Answer

It’s due to your case, it should be like this:

 CASE
      WHEN A.TotalDist > 50 THEN 'High Kms' 
      WHEN A.TotalDist < 10 THEN 'Low Kms'
   END

Instead of:

 CASE A.TotalDist
      WHEN A.TotalDist > 50 THEN 'High Kms' 
      WHEN A.TotalDist < 10 THEN 'Low Kms'
   END

Query:

SELECT A.CarID, A.TotalDist, 
   CASE
      WHEN A.TotalDist > 50 THEN 'High Kms' 
      WHEN A.TotalDist < 10 THEN 'Low Kms'
   END High_Low
FROM (
   SELECT CarID, SUM(Kms) AS TotalDist
   FROM CarUse
   GROUP BY CarID
) AS A

Demo

Advertisement

Answer