0

I tried to make a subselect in a MySQL selct statement e.g. like the following one:

SELECT
  c.id AS id,
  c.name AS name,
  (SELECT count(*) FROM orders o WHERE o.user_id = c.id) AS order_count
FROM
  customers c

Any ideas why this does not work in MySQL? Is it possible to do something like that in MySQL? Tried it in Version 3.23.58 and 5.1.60.

Thanks in advance!

Patrick Schmidt
  • 83
  • 3

4 Answers4

2

You missed , after name, try this:

SELECT
  c.id AS id,
  c.name AS name,
  (SELECT count(*) FROM orders o WHERE o.user_id = c.id) AS order_count
FROM
  customers c
Robert
  • 25,425
  • 8
  • 67
  • 81
2

In order to avoid these sorts of errors (like missing commas), I like to write queries out like this...

    SELECT c.id 
         , c.name 
         , COALESCE(COUNT(o.user_id),0) order_count
      FROM customers c
      LEFT
      JOIN orders o
        ON o.user_id = c.id
     GROUP
        BY c.id;
Strawberry
  • 33,750
  • 13
  • 40
  • 57
1
SELECT
  c.id AS id ,
  c.name AS name ,
 (SELECT count(*) 
   FROM orders o 
   WHERE o.user_id = c.id) AS order_count
FROM
 customers c

Use ',' after every column

Viru
  • 525
  • 2
  • 7
  • 15
0

Another method using joins

Sample data:

ID  NAME
1   john
2   jack
3   kelly

ID  USERID
10  1
11  2
12  1
13  3
14  2
15  3
16  2
17  4
18  1

Query:

SELECT count(o.userid), c.id, c.name
FROM cusomter c
LEFT JOIN orders o
ON c.id = o.userid
GROUP BY c.id
;

You may add case when to represent null as a 0 if a customer doesen't have any orders. You choice.

Results:

COUNT(O.USERID)     ID  NAME
3                   1   john
3                   2   jack
2                   3   kelly
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • @Patrick I wrote this query having a thought on another method as well as **[performance on Joins vs Subquery in MYSQL](http://stackoverflow.com/questions/2577174/join-vs-subquery)**. Check this link out ;) – bonCodigo Jan 07 '13 at 14:05