0

I have a query that returns exactly what I want when there is only one type of group by response. When there is more than one I get the "Subquery returns more than 1 row" error.

I've tried multiple ways of structuring the query including using "in" but couldn't get anything to work.

SELECT 
    (
    SELECT substring(postcode, 1, locate (' ', postcode) - 1)
    FROM user_locations AS ul
    JOIN users AS u ON u.id = ul.user_id
    WHERE u.membertype = "customer"
    ) AS postcode,
    (
    SELECT count(u.membertype)
    FROM users AS u
    JOIN user_locations AS ul ON u.id = ul.user_id
    WHERE u.membertype = "cook"
    GROUP BY ul.postcode

    ) AS cook,
    (
    SELECT count(u.membertype)
    FROM users AS u
    JOIN user_locations AS ul ON u.id = ul.user_id
    WHERE u.membertype = "customer"
    GROUP BY ul.postcode
    ) AS customer

where I have multiple postcodes I would expect to see

Postcode | Cooks | Customers
G83      | 12    | 34 
G84      | 19    | 76
G85      | 10    | 50
SongBox
  • 691
  • 2
  • 8
  • 16

1 Answers1

0

You can do this in a single query by using the expression u.membertype=xxx which is 1 if true otherwise 0.

SELECT ul.postcode AS "Postcode",
       SUM(u.membertype="cook") as "Cooks"
       SUM(u.membertype="customer") as "Customers"
FROM users AS u
JOIN user_locations AS ul ON u.id = ul.user_id
GROUP BY ul.postcode

If there where more than two member types, a WHERE u.membertype IN ("cook","customer") could be added.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • 1
    `count()` should be `sum()`. – Barmar Feb 04 '19 at 23:10
  • if we want to use COUNT() in conditional aggregation, the expression we are counting would need to return NULL for rows we don't want to include in the count, e.g. `COUNT(IF(u.member_type='cook',1,NULL))`. As @Barmar notes, we could do `SUM(u.member_type='cook') AS cooks` as a shortcut. – spencer7593 Feb 04 '19 at 23:17