my mysql query to return multiple totals based on multiple clauses throws up an exception

Question

I am trying to create a query which returns a total count of all listings with the typeID of 1 AND all listings with a typeID of 2 and all listings with a typeID of 3

I'm not sure im on the right lines but i have come up with this

SELECT a.Atotal, b.Btotal, c.Ctotal, u.userID, t.typeID 
FROM 
    (SELECT ul.userID, COUNT( ul.listingID ) AS Atotal
        FROM tbl_user_listing AS ul
        LEFT JOIN tbl_listing_type AS t ON ul.listingID = t.listingID
        WHERE t.typeID =1
        GROUP BY ul.userID
    )AS a,
    (SELECT ul.userID, COUNT( ul.listingID ) AS Btotal
        FROM tbl_user_listing AS ul
        LEFT JOIN tbl_listing_type AS t ON ul.listingID = t.listingID
        WHERE t.typeID =2
        GROUP BY ul.userID
    )AS b,
    (SELECT ul.userID, COUNT( ul.listingID ) AS Ctotal
        FROM tbl_user_listing AS ul
        LEFT JOIN tbl_listing_type AS t ON ul.listingID = t.listingID
        WHERE t.typeID =3
        GROUP BY ul.userID
    )AS c,
FROM tbl_users AS u
WHERE u.userID =1

not surprisingly it is throwing up an exception on line 21

it says #1064 you have an error in your mysql syntax check manual blah de blah near 'FROM tbl_users AS u WHERE u.userID =1' AT LINE 21

can someone help me to understand where i am going wrong please and if this is the best method to achieve my desired result

many thanks

Answer 1

You're missing the JOIN statements between the subqueries. Also, you can't have two FROM clauses. And you're missing the join conditions.

SELECT a.Atotal, b.Btotal, c.Ctotal, u.userID, t.typeID 
FROM users AS u
JOIN (SELECT ul.userID, COUNT( ul.listingID ) AS Atotal
        FROM tbl_user_listing AS ul
        LEFT JOIN tbl_listing_type AS t ON ul.listingID = t.listingID
        WHERE t.typeID =1
        GROUP BY ul.userID
    )AS a ON u.userID = a.userID
JOIN (SELECT ul.userID, COUNT( ul.listingID ) AS Btotal
        FROM tbl_user_listing AS ul
        LEFT JOIN tbl_listing_type AS t ON ul.listingID = t.listingID
        WHERE t.typeID =2
        GROUP BY ul.userID
    )AS b ON u.userID = b.userID
JOIN (SELECT ul.userID, COUNT( ul.listingID ) AS Ctotal
        FROM tbl_user_listing AS ul
        LEFT JOIN tbl_listing_type AS t ON ul.listingID = t.listingID
        WHERE t.typeID =1
        GROUP BY ul.userID
    )AS c ON u.userID = c.userID
WHERE u.userID =1

However, it would be better to combine the subqueries into a single query:

SELECT SUM(t.typeID = 1) AS Atotal, SUM(t.typeID = 2) AS Btotal, SUM(t.typeID = 3) AS Ctotal,
        u.userID, t.typeID
FROM users AS u
JOIN tbl_user_listing AS ul ON ul.userID = u.userID
LEFT JOIN tbl_listing_type AS t ON ul.listingID = t.listingID
WHERE u.userID = 1

I'm guessing t.typeID = 1 was a typo in your original c subquery.