CODEWITHSUNDEEP
mysql
tiltdown
tiltdown

Reputation: 461

How to get row count UNION, SUM MySql

I have this query and I want to get the row count, but I also get MySql error: ERROR 1248 (42000): Every derived table must have its own alias

SELECT SUM(total) FROM
                (
                    SELECT COUNT(1) AS total FROM toys tiw 
                    LEFT JOIN item ti 
                    ON tiw.item_id = ti.id 
                    WHERE tiw.user_id='1785335'
                )
                UNION

                (

                    SELECT COUNT(1) as total 
                    FROM gadgets tfiw 
                    LEFT JOIN flair_store tfs 
                    ON tfiw.flair_id = tfs.id 
                    WHERE tfiw.user_id='1785335'
                ) t1

Upvotes: 0

Views: 945

Answers (1)

Taryn
Taryn

Reputation: 247720

If you are looking to get the sum() of both totals, I believe you want to use:

SELECT SUM(total) 
FROM
(
    SELECT COUNT(1) AS total 
    FROM toys tiw 
    LEFT JOIN item ti 
        ON tiw.item_id = ti.id 
    WHERE tiw.user_id='1785335'
    UNION ALL
    SELECT COUNT(1) as total 
    FROM gadgets tfiw 
    LEFT JOIN flair_store tfs 
        ON tfiw.flair_id = tfs.id 
    WHERE tfiw.user_id='1785335'
) d

Note I would also use UNION ALL. The UNION ALL will return both values even if the total count is the same in both queries. The UNION will remove duplicates.

As far as your error, you are missing an alias on one of your subqueries.

Upvotes: 3

Related Questions