...

View Full Version : SQL selecting rows, group by increment



newmand2
07-25-2006, 07:32 PM
Lets assume the follow database:


| ProductID | Cost |
+-----------+------+
| 1 | 100 |
| 2 | 10 |
| 3 | 250 |
| 4 | 900 |
| 5 | 50 |
| 6 | 60 |
| 7 | 340 |
| 8 | 100 |
+-----------+------+

Is there an SQL function to group those values by a certain increment? For example a predefined increment of 100 would return:


| COUNT | Cost |
+-----------+---------+
| 5 | 0-100 |
| 0 | 101-200 |
| 1 | 201-300 |
| 1 | 301-400 |
| 0 | 401-500 |
| 0 | 501-600 |
| 0 | 601-700 |
| 0 | 701-800 |
| 1 | 801-900 |
+-----------+---------+

Thanks.

Of course, I could loop through each increment with PHP and query each increment but that could get rather slow after a while.

Kid Charming
07-26-2006, 02:54 AM
Not exactly what you asked for, but if you know what your upper range is going to be, you could do this:



SELECT
SUM(CASE
WHEN cost BETWEEN 0 AND 100 THEN 1
ELSE 0
END) AS `0-100`
,SUM(CASE
WHEN cost BETWEEN 101 AND 200 THEN 1
ELSE 0
END) AS `101-200`
,SUM(CASE
WHEN cost BETWEEN 201 AND 300 THEN 1
ELSE 0
END) AS `201-300`
//and so on
FROM
yourtable


That'll give you one row with your counts arranged as one column per incremented group.

Fumigator
07-26-2006, 03:38 AM
This should work:


$query = "
SELECT count(*),
SELECT CASE WHEN Cost BETWEEN 0 AND 100 THEN '0 - 100' ELSE
CASE WHEN Cost BETWEEN 101 AND 200 THEN '101 - 200' ELSE
CASE WHEN Cost BETWEEN 201 AND 300 THEN '201 - 300' ELSE
CASE WHEN Cost BETWEEN 301 AND 400 THEN '301 - 400' ELSE
CASE WHEN Cost BETWEEN 401 AND 500 THEN '401 - 500' ELSE
CASE WHEN Cost BETWEEN 501 AND 600 THEN '501 - 600' ELSE
CASE WHEN Cost BETWEEN 601 AND 700 THEN '601 - 700' ELSE
CASE WHEN Cost BETWEEN 701 AND 800 THEN '701 - 800' ELSE '801+' END END END END END END END END
FROM yourtable
GROUP BY 2";

Kid Charming
07-26-2006, 03:45 AM
That won't give you any rows for zero values, though. You'd have to add them in your script, and if you're going to do that, you may as well just do a regular SELECT * and do all the categorizing/counting in your script (which may not be a bad idea, anyway).

Beagle
07-26-2006, 03:37 PM
group by FLOOR(cost / 100)

BAM!

newmand2
07-26-2006, 09:23 PM
Thanks beagle, I'll give that ago (I'll give them all a go but if I can save time/code ;))

I suppose selecting ALL the costs would be better and then using PHP or something to sort them as such would be the most feasible solution, as it would also reduce the load on the server (right?) if I wanted to repeat the query for different increments.

Thanks very much for the response guys

Kid Charming
07-26-2006, 09:52 PM
I think the GROUP BY FLOOR is going to have the same problem as Fumigator's solution: no rows for zero values.

If I were going to run this with different increments, I'd strongly consider doing it all in PHP. You can make one query and reuse that result set for any number of charts, and I think it would give you the most control and flexibility over your display. You may also be able to set up a separate increments table and use a JOIN, but I think again it would turn out to be more trouble than it's worth. Depending on how much you're going to be using these charts (and how big they're likely to get, though, it may be worth some benchmarking comparisons.

Beagle
07-26-2006, 10:37 PM
Wait, why would the group by floor() not give you rows for zeros? All rows where it equals 0 would be grouped together... I'm not seeing the problem.

Anyway, an increments table would be a lot of trouble and probably cost you a chunk on efficiency as far as I can tell, but I could missing something.

Anyway, really, can someone explain to me this problem with group by not groupin zeroes that I'm not seeing?

Kid Charming
07-26-2006, 11:47 PM
Perhaps I'm misunderstanding the query you're formulating. Can you post the entire query?

Fumigator
07-27-2006, 12:15 AM
The code I posted won't return a result if the count(*) is zero. Pretty much how group by works if you just use a column name.

That's what GJay is saying about your floor code too-- he wasn't saying that "cost = 0" won't be counted and grouped, but that any range that doesn't get a hit won't return a result.

At least I think that's what he was saying... (my brain hurts)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum