Discuss GROUP BY and LIMIT and..... help please! in the MySQL Help forum on Dev Shed. GROUP BY and LIMIT and..... help please! MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
Posts: 1,464
Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526
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.
Posts: 1,464
Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526
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?
Posts: 41
Time spent in forums: 7 h 8 m 57 sec
Reputation Power: 9
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
Posts: 1,464
Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526
Thank you jemagee for your response.
Quote:
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;
Quote:
Honestly - if you are going to do this a lot, create a VIEW
I haven't experimented with views yet. Maybe it is time!
Quote:
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?
Posts: 41
Time spent in forums: 7 h 8 m 57 sec
Reputation Power: 9
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
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
