Menu
I want to count two things under 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 count for rows that have a certain address and certain port, and a SEPARATE count for rows that have a certain name.
I'm aware that I could do
SELECT (COUNT*) as count FROM table_name WHERE (address = ? AND port = ?) OR name = ? However that is a single count, and I need them to be separate so I can display a more accurate message to the user.
How might I go about doing this? Help would be appreciated!
483
You can count multiple COUNT() for multiple conditions in a single query using GROUP BY. SELECT yourColumnName,COUNT(*) from yourTableName group by yourColumnName; To understand the above syntax, let us first create a table.
To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.
SQL COUNT() with HAVING The HAVING clause is used instead of WHERE clause with SQL COUNT() function. The GROUP BY with HAVING clause retrieves the result for a specific group of a column, which matches the condition specified in the HAVING clause.
What about simply:
SELECT SUM(IF(name = ?, 1, 0)) AS name_count, SUM(IF(address = ? AND port = ?, 1, 0)) AS addr_count FROM table_name 
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 = ? If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
