Count number of rows based on multiple criteria

Databases
1

Lets say I have a database of items I’m selling. There are various attributes an item can have, for example an item can labeled as a “green” item or a “made in the usa” item. For the sake of this example, say there are three attributes I’m working with - red, green, and blue. I want to sum up all items that are just red, all items that are green, all items that are blue, all items that are red and green, all items that are red, green and blue, all items that are green, and all items that are green and blue. Instead of running multiple queries to do this, you can use IF() statements in the select statement. Example:

SELECT
    COUNT( IF(items.red = 1) ) AS num_red,
    SUM( IF(items.red = 1 AND items.green = 1, 1, 0) ) AS num_red_green,
    SUM( IF(items.red = 1 AND items.green = 1 AND items.blue = 1, 1, 0) AS num_red_green_blue
    etc....

This seems to work just fine, and it’s much more efficient than running a single query to COUNT() the number of rows that match each individual combination:

SELECT COUNT(id) FROM items WHERE items.red = 1
SELECT COUNT(id) FROM items WHERE items.red = 1 AND items.green = 1
etc...

My question is - is there a more efficient way of doing this than the query I listed above with the IF() statements in the select clause?

Next question: can you do an AND/OR inside of an IF() statement? For example:

SELECT
    COUNT( IF( (items.field1 = 'A' AND items.field2 = 'B') OR (items.field1 = 'C' AND items.field2 = 'D') ) ) AS num

This did not work, I’m getting a syntax error. Is that valid for an IF() statement? I did not see anything in the documentation on the MySQL site about this??

2

Note - I figured out the second part using a CASE statement:

SELECT
    SUM(
        CASE
            WHEN (items.field1 = 'A' AND items.field2 = 'B') OR (items.field1 = 'C' AND items.field2 = 'D') THEN 1
            ELSE 0
        END
    ) AS num_items
FROM....

Is that the most efficient way to do that?

3

most efficient? yes

most elegant? i dunno about that… :wink:

SELECT
    SUM(
        CASE
            WHEN CONCAT(items.field1,items.field2) IN ('AB','CD') 
            THEN 1
            ELSE 0
        END
    ) AS num_items
FROM....