SQL: How to create a table with multiple count functions from the same table.

Question

My table(People) contains the following columns:
ID, FirstName, Status

The data in this table are as follow:
1, Gavin, Online
2, Garth, Offline
3, Christians, Offline
etc.

I want to create a table that produces the count of online and offline people:
Columns: Online, Offline
Rows: 1, 2

I created the following code but it can only produce either Offline or Online but I can't seem to combine both:

SELECT COUNT(Status) as Online
FROM People
WHERE Status="Online";

Help would really be appreciated. Thnx

Answer 1

I just want to conclude that all of you were wrong, I figured out the right way but your answers did motivate me to figure out a solution on my own so thanks.

Solution: (SQL)

SELECT test1.online, test2.offline FROM (SELECT COUNT(Status) AS Online FROM People WHERE Status="Online") AS test1 LEFT OUTER JOIN (SELECT COUNT(Status) AS Offline FROM People WHERE Status="Offline") AS test2 ON test1.online=test2.offline;

Answer 2

remove the "as Online" and replace where clause with "GROUP BY Status"

Answer 3

Yep, GROUP BY should do the trick :)

Answer 4

@Jani Sinkkonen Okay I'll try tomorrow, thanks for your help

Answer 5

nisce

Answer 6

select count(status) over (partition by status) from people

Answer 7

what's the database your using? In MySQL and MariaDB the group by does the trick and Yaroslavs solution is for Oracle I believe.