adding/grouping results from union | Newbie | | Join Date: May 2007
Posts: 5
| | |
I have a problem query that's not playing nice with my webhost's MySQL server. On the face of it it looks quite innocuous but I need a better way as they've forbidden me to run it any more!
Here's the situ: two tables, one has an owner ID and name, the other is for items that are owned, with an index ID and a reference to the owner ID from table 1. That's easy to manage. BUT, this is the killer, in table 2 there's also a reference for a second owner. The idea is I want to generate an ordered list of how many items are owned by each owner, but I need to combine the results of the owner ID appearing in either the first or second owner field.
Here is the (simplified) query that I can't run, using what I thought would be an obvious OR to check the two fields:
SELECT SQL_CALC_FOUND_ROWS owner.name, count(owned.id) AS numowned
FROM owner, owned
WHERE (owned.owner1=owner.id OR owned.owner2=owner.id)
GROUP BY owner.name
ORDER BY numowned DESC
It's been suggested that I can split this into a UNION of two SELECTs for each of the owner1 and owner2 fields, but I then have the problem of combining the results into a count that I can do the order by. At this point I'm stuck. Any suggestions on making this query work without the OR will be gratefully received!
| | Expert | | Join Date: May 2007 Location: India
Posts: 101
| | | re: adding/grouping results from union
Quote:
Originally Posted by asmian I have a problem query that's not playing nice with my webhost's MySQL server. On the face of it it looks quite innocuous but I need a better way as they've forbidden me to run it any more!
Here's the situ: two tables, one has an owner ID and name, the other is for items that are owned, with an index ID and a reference to the owner ID from table 1. That's easy to manage. BUT, this is the killer, in table 2 there's also a reference for a second owner. The idea is I want to generate an ordered list of how many items are owned by each owner, but I need to combine the results of the owner ID appearing in either the first or second owner field.
Here is the (simplified) query that I can't run, using what I thought would be an obvious OR to check the two fields:
SELECT SQL_CALC_FOUND_ROWS owner.name, count(owned.id) AS numowned
FROM owner, owned
WHERE (owned.owner1=owner.id OR owned.owner2=owner.id)
GROUP BY owner.name
ORDER BY numowned DESC
It's been suggested that I can split this into a UNION of two SELECTs for each of the owner1 and owner2 fields, but I then have the problem of combining the results into a count that I can do the order by. At this point I'm stuck. Any suggestions on making this query work without the OR will be gratefully received! Hi Asmian,
I guess you are looking for something like this: -
SELECT owner_name ,SUM(owned)
-
FROM (SELECT owner_name, COUNT(*) AS owned
-
FROM owner_details,item_details
-
WHERE owner1=id
-
GROUP BY owner_name
-
UNION ALL
-
SELECT owner_name,COUNT(*)
-
FROM owner_details,item_details
-
WHERE owner2=id
-
GROUP BY NAME) X
-
GROUP BY owner_name
-
Please get back if you were looking for something else.
Regards,
Pradeep.
| | Newbie | | Join Date: May 2007
Posts: 5
| | | re: adding/grouping results from union
Hi Pradeep, thanks for the info. Let me look again at this, you've made a substantial edit! It looks much better than the first version you posted, which I couldn't follow at all, the syntax didn't seem familiar for MySQL.
| | Newbie | | Join Date: May 2007
Posts: 5
| | | re: adding/grouping results from union
Quote:
Originally Posted by pradeep kaltari Hi Asmian,
I guess you are looking for something like this:
-
SELECT owner_name ,SUM(owned)
-
FROM (SELECT owner_name, COUNT(*) AS owned
-
FROM owner_details,item_details
-
WHERE owner1=id
-
GROUP BY owner_name
-
UNION ALL
-
SELECT owner_name,COUNT(*)
-
FROM owner_details,item_details
-
WHERE owner2=id
-
GROUP BY NAME) X
-
GROUP BY owner_name
-
Please get back if you were looking for something else.
Regards,
Pradeep.
OK, this looks good. The problem I was having was working out how to add the two results from the two SELECTs together. Can you confirm this is how this should work - in particular, your line 7 doesn't restate the alias "AS owned", is this significant? And the final "X" on line 10, what does that do?
If I add an alias to the first line "SUM(owned) AS sumboth", can I use this to do an "ORDER BY sumboth" at the end of the query too?
| | Expert | | Join Date: May 2007 Location: India
Posts: 101
| | | re: adding/grouping results from union
Quote:
Originally Posted by asmian OK, this looks good. The problem I was having was working out how to add the two results from the two SELECTs together. Can you confirm this is how this should work - in particular, your line 7 doesn't restate the alias "AS owned", is this significant? And the final "X" on line 10, what does that do?
If I add an alias to the first line "SUM(owned) AS sumboth", can I use this to do an "ORDER BY sumboth" at the end of the query too? Hi asmian,
1: The "AS owned" part is not required at line 7. The UNION ALL operator takes the names of the columns from the first SELECT query specified.
2: The final 'X' at line 10 is just an alias to the derived table. As you can see the FROM clause of the outer-most query consists of two SELECT statements. Now, the result of these statements is considered as a table named X. In the outer-most query you can refer to owner_name and SUM(owned) as X.owner_name and SUM(X.owned).
3: You can also add alias to the first line "SUM(owned) AS sumboth", and also you can add ORDER BY clause to it. -
SELECT owner_name ,SUM(owned) AS sumboth
-
FROM (SELECT owner_name, COUNT(*) AS owned
-
FROM owner_details,item_details
-
WHERE owner1=id
-
GROUP BY owner_name
-
UNION ALL
-
SELECT owner_name,COUNT(*)
-
FROM owner_details,item_details
-
WHERE owner2=id
-
GROUP BY NAME) X
-
GROUP BY owner_name
-
ORDER BY sumboth DESC, owner_name ASC
-
I hope this is helpful. Please get back if you have further queries.
Regards,
Pradeep.
| | Newbie | | Join Date: May 2007
Posts: 5
| | | re: adding/grouping results from union
Hi Pradeep
Many thanks! This does exactly what I need, and it's not killing the server, which is the main thing! I've managed to add a "WHERE X.owner_name LIKE 'a%'" (etc.) successfully too, which was important.
What I need to do now is add a "LIMIT limitmin,limitmax" for the whole query, but putting it at the end is restricting the number of returned results to the limitmax number, which isn't quite what I expected. I guess the UNION is making this a little tricky and it's probably now fussy about the placing. Can it be done?
| | Newbie | | Join Date: May 2007
Posts: 5
| | | re: adding/grouping results from union
Heh, forgot my SQL_CALC_FOUND_ROWS after the first SELECT!
All working now, brilliant help Pradeep and many thanks.
| | Expert | | Join Date: May 2007 Location: India
Posts: 101
| | | re: adding/grouping results from union
Glad to help you.
Regards,
Pradeep.
|  |
Similar MySQL Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
