#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535

    GROUP BY and LIMIT and..... help please!


    I wish to GROUP my table and sum a column. Okay, that was easy...
    Code:
    SELECT name, sum(amount) AS sum_amount
    FROM orders
    GROUP BY name
    Now the tricky part. If there are 6 or less records, I wish to return all of them. If there are more than 6 records, I wish to return the greatest 5 records, and then return a 6th record consisting of name "remaining" and sum_amount equal to the sum of the remaining records. If this cannot be done solely with SQL, it could use PHP as well.

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    You can do this in MySQL of course but what it sounds like to me is that you need to use two queries, and union, to get your ''sum of the remaining"

    Or do a tricky sub query to get the sixth row telling it to ignore everything in the first five rows.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    Thanks jemagee,

    My original desire was to return a maximum of 6 rows; either all up to 6 rows, or 5 rows plus the total of the remaining. This seems like it will complicate matters and I am okay with returning either up to 6 rows plus a 7th row if necessary which is the sum of the remaining rows.

    So, the first query will be a GROUP BY name ORDER BY sum_amount ASC LIMIT 6.

    Now, the UNION and second query... Any thoughts how to return sum(amount) which excludes the first 6 maximum values?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    Use a subquery that selects all entries NOT IN (the first query)

    You can still do six rows - the first query finds your first five.

    Honestly - if you are going to do this a lot, create a VIEW that gives you your top 5 - so that your subquery is easy - i.e. if your view lists 'company, total, subtotal'

    Your subquery in the union query selects all company entries NOT IN the view result.

    VIEWS are an amazing tool for things you're going to do repeatedly with things like this

    Sorry I can't be more specific - I cna't usually write 'theoretical' queries without seeing all the tables
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    Thank you jemagee for your response.

    Sorry I can't be more specific - I cna't usually write 'theoretical' queries without seeing all the tables
    Code:
    CREATE  TABLE orders (
      id INT NOT NULL AUTO_INCREMENT ,
      name VARCHAR(45) NULL ,
      amount DECIMAL(7,2) NULL ,
      PRIMARY KEY (id) )
    ENGINE = InnoDB;
    Honestly - if you are going to do this a lot, create a VIEW
    I haven't experimented with views yet. Maybe it is time!

    Use a subquery that selects all entries NOT IN (the first query)
    How do I do this? Do I repeat the first query as a subquery in the UNION query? Or do I run the first query, then iterate over it using server code (PHP, etc) to create the second query, then use server code to add it to the returned array? Or is there a more elegant way to do so?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    Create your view as

    Code:
    CREATE VIEW 'viewname' AS
    SELECT max(id) AS ID, name, sum(total) AS Total from orders
    GROUP BY name
    ORDER BY sum(total) DESC
    Limit 5
    Then you're second query to get 'top 5 + sum of rest'

    Code:
    SELECT name, total FROM 'viewname'
    UNION
    Select "The Rest", sum(total) as total FROM orders
    WHERE id NOT IN (Select id from 'viewname')
    (You could also create a second view using the second query if you wanted)

    So assuming I haven't made a gross mistake - the first query creates a view that will give you the top five vendors at all times (the max(id) is just a trick I use when I want a field without having to group by it). The id you put in the view is used later as part of your subquery to find the total of 'the rest' that aren't in the top 5.

    Subquery's like what I wrote and Views are two vital parts of complex databases - learn to make them your friends

    Comments on this post

    • NotionCommotion agrees : Don't now for sure if I agree, but you are opening my eyes about views!

IMN logo majestic logo threadwatch logo seochat tools logo