September 16th, 2012, 11:12 AM
GROUP BY and LIMIT and..... help please!
I wish to GROUP my table and sum a column. Okay, that was easy...
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.
SELECT name, sum(amount) AS sum_amount
GROUP BY name
September 16th, 2012, 03:04 PM
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.
September 16th, 2012, 07:27 PM
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?
September 16th, 2012, 07:31 PM
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
September 16th, 2012, 07:55 PM
Thank you jemagee for your response.
I haven't experimented with views yet. Maybe it is time!
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR(45) NULL ,
amount DECIMAL(7,2) NULL ,
PRIMARY KEY (id) )
ENGINE = InnoDB;
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?
September 16th, 2012, 08:02 PM
Create your view as
Then you're second query to get 'top 5 + sum of rest'
CREATE VIEW 'viewname' AS
SELECT max(id) AS ID, name, sum(total) AS Total from orders
GROUP BY name
ORDER BY sum(total) DESC
(You could also create a second view using the second query if you wanted)
SELECT name, total FROM 'viewname'
Select "The Rest", sum(total) as total FROM orders
WHERE id NOT IN (Select id from 'viewname')
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