MYSQL Sum() Join Group By

[hcamelion]

I have worked on this problem for hours. It is a bit complicated. I have three tables I need to use to get the right data.

First their is the producerroyaltiesmovieinfo table which is the table I want to sum the copiesSold and totalCopiesSold fields and group by movieTitle in that table only.

I have to sum those fields using the custinvoicemovieinfo table and storeinfo table. This is because storeinfo is where the state field and storeID field are held corresponding to the storeID field in the custinvoicemovieinfo table.

Then I match the movieTitle field from the producerroyaltiesmovieinfo with the movieTitle in the custinvoicemovieinfo table.

I have it almost worked out but because the custinvoicemovieinfo table and the producerroyaltiesmovieinfo table both have the copiesSold and totalCopiesSold fields they are being summed all together instead of JUST the producerroyaltiesmovieinfo table fields.

I need to do a join to accomplish this but I dont want to join those tables for any other reason then finding out which movieTitle is in a certain state.

Here is my sql statement as it is now:

select a.movieTitle, SUM(a.copiesSold) copiesSold, SUM(a.totalCopiesSold) totalCopiesSold from producerroyaltiesmovieinfo as a join custinvoicemovieinfo as b on b.movieTitle = a.movieTitle join storeinfo as c on b.storeID = c.storeID where c.state = 'co' and a.producer = 'Gentlemens DVD' group by a.movieTitle

Here are the tables:

custinvoicemovieinfo:

customerName storeID invoiceDate trackingKey movieTitle totalCopiesSold copiesSold copiesViewed amountOwed
The StoreTest Kiosk 22 2007-06-09 1 Boss1 1 0 10.00
The StoreTest Kiosk 21 2007-06-09 1 Weird 1 1 0 10.00
The StoreTest Kiosk 25 2007-06-09 1 Grannies1 1 0 10.00
The StoreTest Kiosk 20 2007-06-09 1 Windixie2 2 0 20.00
The StoreTest Kiosk 22 2007-06-09 1 In Search Of A Dog1 1 0 10.00
The StoreTest Kiosk 21 2007-06-09 1 Mouthwash1 0 10.00
The StoreTest Kiosk 21 2007-06-09 1 Little Dragon2 2 0 20.00
The StoreTest Kiosk 21 2007-06-09 1 Jammin With Bob 1 1 0 10.00
The StoreTest Kiosk 21 2007-06-09 1 Little Dragon1 1 0 10.00

producerroyaltiesmovieinfo:

producer paymentDate trackingKey movieTitle totalCopiesSold copiesSold copiesViewed royalties
Gentlemens DVD 2007-06-10 2 Boss 1 1 0 2.00
Gentlemens DVD 2007-06-10 2 Weird 1 1 0 2.00
Gentlemens DVD 2007-06-10 2 Grannies 1 1 0 2.00
Gentlemens DVD 2007-06-10 2 Windixie2 2 0 4.00
Gentlemens DVD 2007-06-10 2 In Search Of A a Dog1 1 0 2.00
Gentlemens DVD 2007-06-10 2 Mouthwash1 0 2.00
Gentlemens DVD 2007-06-10 2 Little Dragon2 2 0 4.00
Gentlemens DVD 2007-06-10 2 Jammin With Bob 1 1 0 2.00
Gentlemens DVD 2007-06-10 2 Little Dragon1 1 0 2.00

storeinfo:

customerName storeID state 
The StoreTest Kiosk 20 HI 
The StoreTest Kiosk 25 CO 
The StoreTest Kiosk 21 CO 
The StoreTest Kiosk 22 CO

Correct Output:

State CO (colorado)
Producer Gentlemens DVD

movieTitle ¦ totalCopiesSold ¦ copiesSold
* Boss¦ 1 ¦ 1
* Grannies¦ 1 ¦ 1
* In Search Of A Dog ¦ 1 ¦ 1
* Little Dragon¦ 3 ¦ 3
* Mouthwash¦ 1 ¦ 1
* Jammin With Bob¦ 1 ¦ 1
* Weird ¦ 1 ¦ 1

Notice Little Dragon is grouped and the copies sold fields are summed but only for the producerroyaltiesmovieinfo. Also notice Windixie is excluded because it is in the state HI and the above is for the state CO. Here is an example of the output now:

Wrong Output:

movieTitle ¦ totalCopiesSold ¦ copiesSold
* Boss¦ 1 ¦ 1
* Grannies¦ 1 ¦ 1
* In Search Of A Dog ¦ 1 ¦ 1
* Little Dragon¦ 6 ¦ 6
* Mouthwash¦ 1 ¦ 1
* Jammin With Bob¦ 1 ¦ 1
* Weird ¦ 1 ¦ 1

Notice Little Dragon copies sold fields are double what the should be because the join with the group by and sum are adding up the values in the producerroyaltiesmovieinfo and the custinvoicemovieinfo tables and not JUST the producerroyaltiesmovieinfo table.

I also know this table structure is not very good considering it is using natural keys and may have redundant data making it not even in 1st normal form but I don't have control over that. I am just interfacing with a db the customer already uses.

Does anyone know how I can limit the group by and sum() to only the producerroyaltiesmovieinfo table while using a join to only pull records from a certain state and producer.

[hcamelion]

Well I finally found the answer. No one posted. Maybe it's a bit of a complicated question or I made it that way. I noticed over the last few days my post only dropped a few spaces in the board so I guess the database board is a bit of a dust town. I only had one response on another support forum too.

Anyway for anyone else looking to do something similar. Here is how you can use the functionality of a join without joining the tables. It comes down to a sub query. Here is the solution to my problem as stated above:

select movieTitle, SUM(copiesSold) copiesSold, SUM(totalCopiesSold) totalCopiesSold from producerroyaltiesmovieinfo WHERE movieTitle IN (select a.movieTitle from custinvoicemovieinfo as a join storeinfo as b on a.storeID = b.storeID where b.state = 'co') and producer = 'Gentlemens DVD' group by movieTitle

After the statement "WHERE movieTitle IN" is the subquery. Sub queries have to be in parenthesis. This kind of pipes that subquery select into the first select and the "IN" part searches in the sunqueries results.

Basically saying select these fields from the producerroyaltiesmovieinfo table where movietite exists in the result of the subquery.