Can't get a simple divide select statement to work in SQLite

Question

I have a the following code

SELECT DISTINCT M.First_Name || ' ' || M.Surname AS Manager,
                SUM(CASE WHEN MC.Team = F.Home_Team AND 
                              MC.Start_Date <= F.Date AND 
                              MC.End_Date >= F.Date THEN 1 WHEN MC.Team = F.Away_Team AND 
                                                                MC.Start_Date <= F.Date AND 
                                                                MC.End_Date >= F.Date THEN 1 ELSE 0 END) AS Games,
                SUM(CASE WHEN S.HTFT > S.ATFT AND 
                              F.Home_Team = 48 AND 
                              MC.Start_Date <= F.Date AND 
                              MC.End_Date >= F.Date THEN 3 WHEN S.ATFT > S.HTFT AND 
                                                                F.Away_Team = 48 AND 
                                                                MC.Start_Date <= F.Date AND 
                                                                MC.End_Date >= F.Date THEN 3 WHEN S.HTFT = S.ATFT AND 
                                                                                                  MC.Start_Date <= F.Date AND 
                                                                                                  MC.End_Date >= F.Date THEN 1 ELSE 0 END) AS Points,
                (SUM(1.0 * CASE WHEN S.HTFT > S.ATFT AND 
                                     F.Home_Team = 48 AND 
                                     MC.Start_Date <= F.Date AND 
                                     MC.End_Date >= F.Date THEN 3 WHEN S.ATFT > S.HTFT AND 
                                                                       F.Away_Team = 48 AND 
                                                                       MC.Start_Date <= F.Date AND 
                                                                       MC.End_Date >= F.Date THEN 3 WHEN S.HTFT = S.ATFT AND 
                                                                                                         MC.Start_Date <= F.Date AND 
                                                                                                         MC.End_Date >= F.Date THEN 1 ELSE 0 END) / SUM(CASE WHEN MC.Team = F.Home_Team AND 
                                                                                                                                                                  MC.Start_Date <= F.Date AND 
                                                                                                                                                                  MC.End_Date >= F.Date THEN 1 WHEN MC.Team = F.Away_Team AND 
                                                                                                                                                                                                    MC.Start_Date <= F.Date AND 
                                                                                                                                                                                                    MC.End_Date >= F.Date THEN 1 ELSE 0 END) ) AS PPG
  FROM Manager AS M
       LEFT JOIN
       Manager_Contract AS MC ON M.ID = MC.Manager
       LEFT JOIN
       Fixture AS F ON MC.Team = F.Home_Team OR 
                       MC.Team = F.Away_Team
       LEFT JOIN
       Score AS S ON F.ID = S.Fixture
 GROUP BY M.First_Name || ' ' || M.Surname
HAVING Games > 0
 ORDER BY MC.Start_Date ASC;

Which returns

Manager	Games	Points	PPG
Ben Garner	10	12	1.2
Paul Tisdale	15	12	0.8
Tommy Widdrington	3	3	1
Joey Barton	18	11	0.61111

The last column I want to return

PPG
1.20
0.80
1.00
0.61

Every time I try to add ROUND in I get an error message - Usually Row value misused.

Any help appreciated.

I am using SQLite Studio and MacBook Pro.

QUESTION EDITED - Managed to fix initial issue.

Answer 1

The error message:

Row value misused

is often the result of incorrect usage of parentheses and this may happen because your code contains many conditions in all the branches of the CASE expressions.

I think that you can simplify the code.

The condition in the HAVING clause:

HAVING Games > 0

will filter out any managers for which this CASE expression:

CASE 
  WHEN MC.Team = F.Home_Team 
   AND MC.Start_Date <= F.Date 
   AND MC.End_Date >= F.Date THEN 1 
  WHEN MC.Team = F.Away_Team 
   AND MC.Start_Date <= F.Date 
   AND MC.End_Date >= F.Date THEN 1 
  ELSE 0 
END

is always 0.

So, why don't you set a WHERE clause, equivalent to the above conditions? Also, this makes the first 2 LEFT joins actually INNER joins (and I'm not sure if the last join should be a LEFT join).

Try this simplified code:

SELECT M.First_Name || ' ' || M.Surname AS Manager,
       COUNT(*) AS Games,
       SUM(
         CASE 
           WHEN (S.HTFT > S.ATFT AND F.Home_Team = 48) OR (S.ATFT > S.HTFT AND F.Away_Team = 48) THEN 3 
           WHEN S.HTFT = S.ATFT THEN 1 
           ELSE 0 
         END
       ) AS Points,
       ROUND(
         1.0 *
         SUM(
           CASE 
             WHEN (S.HTFT > S.ATFT AND F.Home_Team = 48) OR (S.ATFT > S.HTFT AND F.Away_Team = 48) THEN 3 
             WHEN S.HTFT = S.ATFT THEN 1 
             ELSE 0 
           END
         ) / 
         COUNT(*)
         , 2
       ) AS PPG
FROM Manager AS M
INNER JOIN manager_Contract AS MC ON M.ID = MC.Manager
INNER JOIN Fixture AS F ON MC.Team IN (F.Home_Team, F.Away_Team)
LEFT JOIN Score AS S ON F.ID = S.Fixture
WHERE MC.Start_Date <= F.Date AND MC.End_Date >= F.Date
GROUP BY Manager;