MySQL - counting two things with different conditions

Question

I want to count two things in different conditions in one query.

SELECT COUNT(*) AS count FROM table_name WHERE name = ? 

and

 SELECT COUNT(*) as count FROM table_name WHERE address = ? AND port = ? 

I need to have a counter for strings that have a specific address and a specific port, and a SEPARATE account for strings with a specific name.

I know what I can do

 SELECT (COUNT*) as count FROM table_name WHERE (address = ? AND port = ?) OR name = ? 

However, this is the only account, and I need them to be separate, so I can display a more accurate message for the user.

How can i do this? Help will be appreciated!

Answer 1

How about just:

 SELECT SUM(IF(name = ?, 1, 0)) AS name_count, SUM(IF(address = ? AND port = ?, 1, 0)) AS addr_count FROM table_name 

Answer 2

 SELECT SUM(CASE WHEN Name = ? THEN 1 ELSE 0 END) as name_match , SUM(CASE WHEN Address = ? AND Port = ? THEN 1 ELSE 0 END) as address_match FROM table_name WHERE (address = ? AND port = ?) OR name = ? 

Answer 3

It may be easiest to make an alliance:

 SELECT COUNT(*) AS count FROM table_name WHERE name = ? GROUP BY name UNION ALL SELECT COUNT(*) as count FROM table_name WHERE address = ? AND port = ? GROUP BY address, port 

Answer 4

 SELECT COUNT( CASE WHEN n1 = 'J' THEN 1 END ) AS t1, COUNT( CASE WHEN n2 = 'C' THEN 1 END ) AS t2, COUNT( CASE WHEN n3 = 'K' THEN 1 END ) AS t3 FROM test 

Using COUNT(CASE...) , you can get the counter of two columns from the same table, even if the conditions for both are different (for example: get the counter J from column n1 and the counter C from column n2, etc.) ..)

Table: test

 +----+----+----+----+ | id | n1 | n2 | n3 | |----+----+----+----+ | 1 | J | C | K | |----+----+----+----+ | 1 | J | C | F | |----+----+----+----+ | 1 | J | K | C | |----+----+----+----+ | 1 | K | K | C | |----+----+----+----+ 

Result:

 +----+----+----+ | t1 | t2 | t3 | |----+----+----+ | 3 | 2 | 1 | |----+----+----+