adding/grouping results from union

[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!

[pradeep kaltari]

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.

[asmian]

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.

[asmian]

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?

[pradeep kaltari]

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.

[asmian]

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?

[asmian]

Heh, forgot my SQL_CALC_FOUND_ROWS after the first SELECT!

All working now, brilliant help Pradeep and many thanks.

[pradeep kaltari]

Glad to help you.

Regards,
Pradeep.