CODEWITHSUNDEEP
mysqljoinleft-join
Ali Mohammad
Ali Mohammad

Reputation: 1

MySQL - Error #1064 in LEFT JOIN

I am currently trying to get the data formatted using only MySQL query. I do not want to manipulate it using a programming language. All the operations are done only on one table.

Here is my query: 

    SELECT DATE(datetime_column) AS date, COUNT(`id`) AS users_a FROM users a 
    WHERE datetime_column >= '2017-12-01' GROUP BY DATE(datetime_column), rank HAVING rank = "good"

    LEFT JOIN (SELECT DATE(datetime_column) AS date, COUNT(`id`) AS users_b FROM users
    WHERE datetime_column >= '2017-12-01' GROUP BY DATE(datetime_column), rank HAVING rank = "bad") AS b 
    ON b.date = a.date;

The output should be like:

date           users_a           users_b
2017-12-01     17                20
2017-12-02     4                 9

The two queries give accurate results if they are executed separately, and I just want the data to be in one table. However, I get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT DATE(datetime) AS date, COUNT(`id`) AS users_b FROM ' at line 2

Upvotes: 0

Views: 495

Answers (2)

kchason
kchason

Reputation: 2885

JOINs must occur before the HAVING clause.

Per the MySQL SELECT documentation:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

Plus, you could also simplify it into something like:

SELECT
    DATE(`datetime_column`) AS `date`, 
    SUM(IF(`rank` = 'good', 1, 0)) AS `users_a`,
    SUM(IF(`rank` = 'bad', 1, 0)) AS `users_b`
FROM
    `users`
WHERE
   datetime_column >= '2017-12-01'
GROUP BY
    DATE(datetime_column)

Which increments the users_a column for every 'good' user, and users_b for every 'bad'.

Upvotes: 1

Hasaan Mubasher
Hasaan Mubasher

Reputation: 160

Try this

SELECT DATE(datetime_column) date, SUM(CASE WHEN rank = "good" THEN 1 END) users_a, SUM(CASE WHEN rank = "bad" THEN 1 END) users_b
FROM users
WHERE datetime_column>= '2017-12-01'
GROUP BY DATE(datetime_column)

Upvotes: 0

Related Questions