[RESOLVED] Stratifying Data

[nhero]

I am using the following query in MySql to stratify data, however I can only use 9 sum ifs. Once I add the 10th sum if I receive the following error: Query returned no resultset. When only 9 are used it works fine.

"Select ID,

Sum(if(score < 600, balance, 0)) /sum(Balance) * 100 as Balance_Less_Than_600,

Sum(if( score between 600 and 640, balance, 0)) /sum(Balance) * 100 as balance_600_640,

Sum(if( score between 640 and 660, balance, 0)) /sum(Balance) 100 as balance_640_660,
Sum(if( score between 660 and 680, balance, 0)) /sum(Balance) 100 as balance_660_680,
Sum(if( score between 680 and 700, balance, 0)) /sum(Balance) 100 as balance_680_700,
Sum(if( score between 700 and 720, balance, 0)) /sum(Balance) 100 as balance_700_720,
Sum(if( score between 720 and 740, balance, 0)) /sum(Balance) 100 as balance_720_740,
Sum(if( score between 740 and 760, balance, 0)) /sum(Balance) 100 as balance_740_760,
Sum(if( score between 760 and 1000, balance, 0)) /sum(Balance) * 100 as balance_760_1000

from tbl_test_data where ID = 'test_data' group by ID"

I would like to be able to add a sum if for 620 to 640.

Is there a better way to set up the query? Any thoughts as to why MySql can only handle 9 sum ifs?

[Installer]

It works fine for me on a table filled with random test data.

I think you have a problem, though, with your use of BETWEEN. The results of that function are inclusive. So, e.g., if you have a score of 700 it will be retuned in both "balance_680_700" and "balance_700_720" (which I don't think you want). Something like this should work better:

...
SUM(IF(score BETWEEN 680 AND 699, balance, 0)) / SUM(balance) * 100 AS balance_680_699,
SUM(IF(score BETWEEN 700 AND 719, balance, 0)) / SUM(balance) * 100 AS balance_700_719,
...

[nhero]

The query listed above works however when I use this query it doesn't work.

"Select ID,

Sum(if(score < 600, balance, 0)) /sum(Balance) * 100 as Balance_Less_Than_600,

Sum(if( score between 600 and 620, balance, 0)) /sum(Balance) 100 as balance_600_620,
Sum(if( score between 620 and 640, balance, 0)) /sum(Balance) 100 as balance_640_640,
Sum(if( score between 640 and 660, balance, 0)) /sum(Balance) 100 as balance_640_660,
Sum(if( score between 660 and 680, balance, 0)) /sum(Balance) 100 as balance_660_680,
Sum(if( score between 680 and 700, balance, 0)) /sum(Balance) 100 as balance_680_700,
Sum(if( score between 700 and 720, balance, 0)) /sum(Balance) 100 as balance_700_720,
Sum(if( score between 720 and 740, balance, 0)) /sum(Balance) 100 as balance_720_740,
Sum(if( score between 740 and 760, balance, 0)) /sum(Balance) 100 as balance_740_760,
Sum(if( score between 760 and 1000, balance, 0)) /sum(Balance) * 100 as balance_760_1000

from tbl_test_data where ID = 'test_data' group by ID"

[Installer]

Sorry, that exact query (copy-pasted) works perfectly for me (except for the "between" problem). What "doesn't work"? Do you still get the same message?

[nhero]

I forgot to index my table. Once it was indexed properly I no longer had a problem. Thanks