MySQL query using sum and join-CodePudding

Newbie, syntax question: I’m trying to join two tables, readingLog and students. Both columns share stSponsorId. I’d like to use SUM to get a total min read, weekly minutes read and read today value from the many entries in readingLog for each student. I’m having trouble with the second and third SUM statements. They keep throwing syntax errors. I’ve tried different date statements, parentheses, putting all of the columns requested—including the sums in the first SELECT, but I haven’t figured it out. I’d appreciate any advice. Here to learn. Thanks.

SELECT 
        s.grade,
        s.readingGoal,
        s.stFirstName,
        s.stLastName,
        s.teacher  
        FROM students s
        LEFT JOIN readingLog rL (
           SELECT 
            SUM(rL.minutes) readTotal,
            SUM(
                CASE
                WHEN (DATE(rL.dateRead) >= NOW() - INTERVAL 1 DAY)
                THEN rL.minutes
                ELSE 0
                END
            ) AS readToday,
            SUM(
                CASE
                WHEN (DATE(rL.dateRead) > NOW() - INTERVAL 7 DAY)
                THEN rL.minutes
                ELSE 0
                END
                ) AS readWeek
                GROUP BY rL.stSponsorId)
             USING(stSponsorId);

CodePudding user response：

I think you were on a good start, but then confused on the left-join. You were trying to left join the reading log directly, then have a subquery THINKING that was coming from the reading log. Your sub query SHOULD be the reading log and join on the result of that as an alias. Something like.

SELECT 
      s.grade,
      s.readingGoal,
      s.stFirstName,
      s.stLastName,
      s.teacher,
      RLSums.readTotal,
      RLSums.readToday,
      RLSums.readWeek
   FROM 
      students s
         LEFT JOIN 
         ( SELECT
                 rl.stSponsorId,
                 SUM(rL.minutes) readTotal,
                 SUM( CASE WHEN DATE(rL.dateRead) >= NOW() - INTERVAL 1 DAY
                           THEN rL.minutes ELSE 0 END ) AS readToday,
                 SUM( CASE WHEN DATE(rL.dateRead) > NOW() - INTERVAL 7 DAY
                           THEN rL.minutes ELSE 0 END ) AS readWeek
              from
                 readingLog rL 
              GROUP BY 
                 rL.stSponsorId ) as RLSums
             on s.stSponsorId = RLSums.stSponsorId)

Notice the LEFT JOIN is an entire query of its own support. In this case, I gave it a final alias of "RLSums". So the Students table is LEFT JOINed to the RLSums RESULT query. Make sense?

This way, you can always pre-test a query of its own to see that ITS syntax is ok and you get the expected result before applying join to the next level.

CodePudding user response：

There are multiple problems with your query. First of all for your inner query is missing the FROM part and you give both a table(readingLog) and a query(the inner query) after the LEFT JOIN as arguments. As the Join Operator only excepts one argument on its right side you can probably cut "readingLog rL" and add it in the FROM part in the sub query.

Secondly you are not joining on any attributes, which is syntactically speaking not wrong, however results in a cross join, which is (probably) not what you want/should do, as it matches everything with everything in both tables.

Thirdly your outer query only SELECTS columns from your first table, which seems odd.

Here is syntactically working code, however I'm not sure if it does semantically exactly what you want:

SELECT 
        s.grade,
        s.readingGoal,
        s.stFirstName,
        s.stLastName,
        s.teacher  
FROM students s
LEFT JOIN ( 
    SELECT 
        SUM(rL.minutes) AS readTotal,
        SUM(
            CASE
            WHEN (DATE(rL.dateRead) >= NOW() - INTERVAL 1 DAY)
            THEN rL.minutes
            ELSE 0
            END
        ) AS readToday,
        SUM(
            CASE
            WHEN (DATE(rL.dateRead) > NOW() - INTERVAL 7 DAY)
            THEN rL.minutes
            ELSE 0
            END
            ) AS readWeek
     FROM readingLog rL
     GROUP BY rL.stSponsorId)
USING(stSponsorId);