Welcome to WebmasterWorld Guest from 54.242.193.41

Forum Moderators: open

Multiple SELECT results in one row (same table)

     
7:21 am on Jun 3, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1116
votes: 110


I know, it's 3am and I shouldn't be coding this late :-( So this is probably a stupid question, and I'll be embarrassed to have asked it tomorrow.

But here I am :-(

I have a query that looks like this:

SELECT 
(SELECT COUNT(1) FROM table WHERE username = 'csdude' AND status = 'active' AND expiration >= 20190602) AS active,
(SELECT COUNT(1) FROM table WHERE username = 'csdude' AND status = 'active' AND expiration < 20190602) AS expired,
(SELECT COUNT(1) FROM table WHERE username = 'csdude' AND status = 'deleted') AS deleted


I need the 3 separate counts as 3 columns in one result. And it will always just have the one row for the result, because this only applies to the logged-in user.

An alternative that I considered is:

SELECT COUNT(1) FROM table WHERE username = 'csdude' AND status = 'active' AND expiration >= 20190602
UNION
SELECT COUNT(1) FROM table WHERE username = 'csdude' AND status = 'active' AND expiration < 20190602
UNION
SELECT COUNT(1) FROM table WHERE username = 'csdude' AND status = 'deleted'


but that puts the results in 1 column / 3 rows instead of 3 columsn / 1 row. Which I could work with, but I don't know if it's "better" than the original.

Is there a better / faster way to run this query?