percentage of rows that match condition

database, mysql

Solution

Assuming the `table1` and `table` are actually meant to be the same.. you could try

SELECT 100*SUM(CASE WHEN `isMember` THEN 1 ELSE 0 END)/COUNT(*) percent_new
FROM `table1`
WHERE `date` BETWEEN @monthbegin AND @monthend

Absolutely no idea if this is faster than your query, but it does remove the subqueries.

You could also try:

SELECT 100*COUNT(CASE WHEN `isMember` THEN 1 ELSE NULL END)/count(*) percent_new
FROM `table1`
WHERE `date` BETWEEN @monthbegin AND @monthend

Actually, using the way `MySQL` treats `BOOLEAN` columns you can get away with:

SELECT 100*SUM(`isMember`)/COUNT(*) percent_new
FROM `table1`
WHERE `date` BETWEEN @monthbegin AND @monthend

Problem

I am looking for a way to get a percentage of rows that match a certain condition without utilizing subqueries. Currently, I have: ``` SELECT ((SELECT COUNT(*) FROM `table1` WHERE `date` >= @monthbegin AND `date` <= @monthend AND `isMember` = TRUE) / (SELECT COUNT(*) FROM `table` WHERE `date` >= @monthbegin AND `date` <= @monthend) * 100) AS percent_new ``` Basically, there are `N` new rows each month, and I would like to get the percentage of these new rows that match the condition where `isMember = TRUE`. Is there a shorter or cleaner way to do this?

Original source