How to get 1 row result instead of 2

Question

I'm trying to grab 2 count values from a union all query and then combine the sum of those 2 values so i then have a total count. The query at the moment is giving me 2 rows of data which is 1 for each of the queries.

How can i update the below query to only display 1 row with the total count of the 2 queries.

SELECT SUM(qty) as qty
FROM (
    SELECT COUNT(f.unread) as qty
    FROM users u, followers f
    WHERE u.uid = f.follower AND f.uid = '605bb0e3d8fb16.55214369' AND f.unread = 'Y' GROUP BY f.unread
    UNION ALL
    SELECT COUNT(f.unread) as qty
    FROM users u, comments f, subs s
    WHERE u.uid = f.uid AND s.sid = f.sid AND s.uid = '605bb0e3d8fb16.55214369' AND f.uid <> '605bb0e3d8fb16.55214369' AND f.unread = 'Y' GROUP BY f.unread
) t
GROUP BY t.qty

Thanks in advance

and the two GROUP BYs in the union aren't needed – ysth Jun 23 '21 at 23:44 — ysth, Jun 23 '21 at 23:44
Thank you!!! That was it, worked a charm thanks. – TRicks Jun 23 '21 at 23:44 — TRicks, Jun 23 '21 at 23:44

score 1 · Answer 1 · edited Jun 24 '21 at 00:55

try not to use the old

select something
from table1, table2, table3
where table1.something = table2.something

Instead list your joins properly:

SELECT SUM(qty) as qty
FROM (
    SELECT COUNT(f.unread) as qty
    FROM users u
    JOIN followers f ON u.uid = f.follower f.uid = '605bb0e3d8fb16.55214369' AND f.unread = 'Y'
    UNION ALL
    SELECT COUNT(f.unread) as qty
    FROM users u
    JOIN comments f ON u.uid = f.uid AND f.uid <> '605bb0e3d8fb16.55214369' AND f.unread = 'Y' 
    JOIN subs s ON s.sid = f.sid AND s.uid = '605bb0e3d8fb16.55214369'
) t

Once you get used to it the new method is a lot easier to follow especially in larger queries

You can move the non table link clauses ie AND s.uid = '605bb0e3d8fb16.55214369' out to a where clause at the end if you like but I like to keep anything relating to a table set for joining purposes in the join itself for readability. It's discussed a bit here: Condition within JOIN or WHERE and keeping the filter in the join can give a perfomance boost but I am not sure how much of a boost we are talking, I just find it easier to read and maintain this way.

score 0 · Answer 2 · answered Jun 24 '21 at 00:14

When there is no GROUP BY, aggregate functions such as SUM work on all rows. So you simply need to remove the outer GROUP BY to sum the results of the two queries.

The inner GROUP BY clauses are also unneeded, though not harmful, since there is only one possible value for f.unread in each of the two unioned queries.

Alternatively, you can simply add the results instead of using SUM/UNION:

SELECT (
    SELECT COUNT(f.unread) as qty
    FROM users u, followers f
    WHERE u.uid = f.follower AND f.uid = '605bb0e3d8fb16.55214369' AND f.unread = 'Y'
) + (
    SELECT COUNT(f.unread) as qty
    FROM users u, comments f, subs s
    WHERE u.uid = f.uid AND s.sid = f.sid AND s.uid = '605bb0e3d8fb16.55214369' AND f.uid <> '605bb0e3d8fb16.55214369' AND f.unread = 'Y'
);

since you can use a parenthesized subquery in an expression as long as it returns only one column and one row.

How to get 1 row result instead of 2

2 Answers2