Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jan 2004
    Location
    London, UK
    Posts
    234
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL selecting rows, group by increment

    Lets assume the follow database:

    Code:
    | 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:

    Code:
    | 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.
    Dave

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not exactly what you asked for, but if you know what your upper range is going to be, you could do this:

    Code:
    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.

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    This should work:
    PHP Code:
    $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"


  • #4
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).

  • #5
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    group by FLOOR(cost / 100)

    BAM!

  • #6
    Regular Coder
    Join Date
    Jan 2004
    Location
    London, UK
    Posts
    234
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Dave

  • #7
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #8
    Senior Coder
    Join Date
    Jul 2005
    Location
    New York, NY
    Posts
    1,084
    Thanks
    4
    Thanked 19 Times in 19 Posts
    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?

  • #9
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perhaps I'm misunderstanding the query you're formulating. Can you post the entire query?

  • #10
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •