CODEWITHSUNDEEP
mysqlsqlmysql-error-1064
Cyclone
Cyclone

Reputation: 15269

Query syntax error in MySQL query

This is my query:

SELECT
   `i`.`itemtype` AS `Item`,
   `p`.`name`
   (SELECT SUM(`i`.`count`) AS `Count` WHERE `itemtype` = 2148),
   (SELECT SUM(`i`.`count`) * 100 AS `Count1` WHERE `itemtype` = 2152),
   (SELECT SUM(`i`.`count`) * 10000 AS `Count2` WHERE `itemtype` = 2160)
FROM `player_items` AS `i`
LEFT JOIN `players` AS `p` ON (`p`.`id` = `i`.`player_id`)
WHERE `i`.`itemtype` IN (2148, 2152, 2160)
GROUP BY `i`.`itemtype`
LIMIT 0, 30

When I run the above query in mysql, I get this error message:

#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
'SELECT SUM(`i`.`count`) AS `Count` WHERE `itemtype` = 2148)
   , (SELECT SUM(`i`' at line 4

I completely don't understand what does that mean as I'm a begginer in MySQL.

Upvotes: 1

Views: 2731

Answers (5)

Adam Wenger
Adam Wenger

Reputation: 17540

You're missing a comma after this column:

`p`.`name`

Consider this query instead:

SELECT i.itemtype AS Item
   , p.name
   , SUM(CASE 
            WHEN itemtype = 2148 THEN i.count
            ELSE 0
         END) AS Count
   , SUM(CASE
            WHEN itemtype = 2152 THEN i.count
            ELSE 0
         END) * 100 AS Count1
   , SUM(CASE
            WHEN itemtype = 2160 THEN i.count
            ELSE 0
         END) * 10000 AS Count2
FROM player_items AS i
LEFT JOIN players AS p ON i.player_id = p.id
WHERE i.itemtype IN (2148, 2152, 2160)
GROUP BY i.itemtype ASC
LIMIT 0, 30

To SUM those three columns together, you could:

SELECT t.itemType AS Item, t.Count, t.Count1, t.Count2
   , SUM(t.Count1, t.Count2, t.Count3) AS Total
FROM
(
   SELECT i.itemtype AS Item
      , p.name
      , SUM(CASE 
               WHEN itemtype = 2148 THEN i.count
               ELSE 0
            END) AS Count
      , SUM(CASE
               WHEN itemtype = 2152 THEN i.count
               ELSE 0
            END) * 100 AS Count1
      , SUM(CASE
               WHEN itemtype = 2160 THEN i.count
               ELSE 0
            END) * 10000 AS Count2
   FROM player_items AS i
   LEFT JOIN players AS p ON i.player_id = p.id
   WHERE i.itemtype IN (2148, 2152, 2160)
   GROUP BY i.itemtype ASC
   LIMIT 0, 30
) AS t

Or you could add another CASE

SELECT i.itemtype AS Item
   , p.name
   , SUM(CASE 
            WHEN itemtype = 2148 THEN i.count
            ELSE 0
         END) AS Count
   , SUM(CASE
            WHEN itemtype = 2152 THEN i.count
            ELSE 0
         END) * 100 AS Count1
   , SUM(CASE
            WHEN itemtype = 2160 THEN i.count
            ELSE 0
         END) * 10000 AS Count2
   , SUM(CASE
            WHEN itemtype = 2148 THEN i.count
            WHEN itemtype = 2152 THEN (i.count * 100)
            WHEN itemtype = 2160 THEN (i.count * 10000)
            ELSE 0
         END) AS Total
FROM player_items AS i
LEFT JOIN players AS p ON i.player_id = p.id
WHERE i.itemtype IN (2148, 2152, 2160)
GROUP BY i.itemtype ASC
LIMIT 0, 30

The CASE statements makes it unnecessary for you to have to run your three additional SELECT statements to return the same results.

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656481

I suppose you want something like this:

SELECT p.name
      ,i.itemtype
      ,CASE itemtype 
         WHEN 2148 THEN count(*)
         WHEN 2152 THEN count(*) * 100
         WHEN 2160 THEN count(*) * 10000
       END AS ct
FROM   players p
LEFT   JOIN player_items i ON p.id = i.player_id
WHERE  i.itemtype IN (2148, 2152, 2160)
GROUP  BY p.name, i.itemtype
ORDER  BY p.name, i.itemtype
LIMIT  30;

Or, maybe this:

SELECT p.name
      ,sum(CASE WHEN itemtype = 2148 THEN 1 ELSE 0 END) AS ct
      ,sum(CASE WHEN itemtype = 2152 THEN 1 ELSE 0 END) * 100 AS ct1
      ,sum(CASE WHEN itemtype = 2160 THEN 1 ELSE 0 END) * 10000 AS ct2
FROM   players p
LEFT   JOIN player_items i ON p.id = i.player_id
WHERE  i.itemtype IN (2148, 2152, 2160)
GROUP  BY p.name
ORDER  BY p.name
LIMIT  30;

See a working demo for this version.

Upvotes: 1

javalixue
javalixue

Reputation: 11

SELECT
   `i`.`itemtype` AS `Item`,
    `p`.`name *<{miss ','}>*
    (SELECT SUM(`i`.`count`) AS `Count` *<miss from 'table'>* WHERE `itemtype` = 2148),
    (SELECT SUM(`i`.`count`) * 100 AS `Count1` *<miss from 'table'>* WHERE `itemtype` = 2152),
    (SELECT SUM(`i`.`count`) * 10000 AS `Count2` *<miss from 'table'>* WHERE `itemtype` = 2160)

Upvotes: 1

ajreal
ajreal

Reputation: 47321

Faulty :-

SELECT SUM(`i`.`count`) AS `Count` <-- missing FROM which table

Correct :-

SELECT SUM(`i`.`count`) AS `Count` FROM some_table ...

Upvotes: 1

Aurelio De Rosa
Aurelio De Rosa

Reputation: 22152

In all of your SELECT SUM(...) you don't define the FROM player_items AS i

Upvotes: 1

Related Questions