MYSQL Sum() Join Group By

[hcamelion]

I have worked on this problem for hours. 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

I hope I explained things as simply as possible. Does anyone have any ideas on this. Thanks.

[guelphdad]

can you show some sample data from your tables and the expected output you want to get?

custinvoicemovieinfo table and the producerroyaltiesmovieinfo table both have the copiesSold and totalCopiesSold fields

they shouldn't. You are storing redundant data if you have the exact same info stored in more than one table.

[hcamelion]

custinvoicemovieinfo:
customerName storeID invoiceDate trackingKey movieTitle totalCopiesSold copiesSold copiesViewed amountOwed
The Store Test Kiosk 22 2007-06-09 1 Boss 1 1 0 10.00
The Store Test Kiosk 21 2007-06-09 1 Weird 1 1 0 10.00
The Store Test Kiosk 25 2007-06-09 1 Grannies 1 1 0 10.00
The Store Test Kiosk 20 2007-06-09 1 Windixie 2 2 0 20.00
The Store Test Kiosk 22 2007-06-09 1 In Search Of A Dog 1 1 0 10.00
The Store Test Kiosk 21 2007-06-09 1 Mouthwash 1 0 10.00
The Store Test Kiosk 21 2007-06-09 1 Little Dragon 2 2 0 20.00
The Store Test Kiosk 21 2007-06-09 1 Jammin With Bob 1 1 0 10.00
The Store Test Kiosk 21 2007-06-09 1 Little Dragon 1 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 Windixie 2 2 0 4.00
Gentlemens DVD 2007-06-10 2 In Search Of A a Dog 1 1 0 2.00
Gentlemens DVD 2007-06-10 2 Mouthwash 1 0 2.00
Gentlemens DVD 2007-06-10 2 Little Dragon 2 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 Dragon 1 1 0 2.00


storeinfo:
customerName storeID state
The Store Test Kiosk 20 HI
The Store Test Kiosk 25 CO
The Store Test Kiosk 21 CO
The Store Test 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 redindant data but I don't have control over that. I am just interfacing with a db the customer already uses. Yes the totalCopiesSold field could be redundant especially if it records the total copies sold to date but not if it records the total copies sold for that specific store on that specific invoice date for that specific movie title with those three fields combining to make a unique key. I have to ask the db creator about that. The copiesSold field is not redundant cause I know that only records the copies sold for the specific movie for the specific rpyalty payment for producers or invoice for customers.

Anyway 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. Thanks for starting to help me guelphdad I guess your the main active poster in this forum. No one else posted. Maybe it's a bit of a complicated question. I noticed over the last few days my post only dropped a few spaces in the board so I guess the mysql board is a bit of a dust town.

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.