Help with generating a JOIN query 2

[c4n]

Hello,

I need some help with a MySQL query, probably a JOIN. Here is the situation:

I have two tables. The first one contains customer info.

Table CUSTOMERS
id | name | address

id ... customer ID (MEDIUMINT, autoincrement)
name ... customer name, for example John Doe
address ... customer shipping address


Then I have the second table which contains sale info from all customers.

Table SALES
id | dt | customer | amount | shipped

id ... is sale ID ( INT, autoincrement)
dt ... date of the sale (DATE, 00-00-0000)
customer ... customer ID number, the same as "id" in table CUSTOMERS
amount ... sale amount in dollars
shipped ... TINYINT, 1 = shipped, 0 = not yet shiped


Now what I want to do is create a query which will contain this info for EACH customer:

Customer ID | Customer name | Customer address | Sum of ALL sales amount | Sum of SHIPPED sales amount


So basically I want a query which will return id, name, address, and two amount sums for each customer. Is such a query possible in this situation?

I tried with JOIN command but couldn't get iw working, any ideas?

Thanks for any help!

 

[cLFlaVA]

This should work:

[tt]
SELECT c.id, c.name, c.address, sum(s.amount), sum(s.shipped)
FROM customers c join sales S
ON c.id = s.customer
GROUP BY c.id, c.name, c.address[/tt]

*cLFlaVA
----------------------------
Breaking the habit...

 

[c4n]

Hello,

Thanks! Actually I just figured it out myself, it didn't work because I was using sum() without GROUP BY.

Best regards,

c4n

 

[c4n]

Ok, I see this is not exactly what I would need, this is a bit more complicated:

Note that SHIPPED is only set to 0 OR 1 (if set o 1 the item has been shipped, if set to 0 it is pending shippment) so I can't use sum(s.shipped) as it would only sum those 0's and 1's.

This is the closest I got:

SELECT c.id, c.name, c.address, sum(s.amount)
FROM customers AS c, sales AS S
WHERE c.id = s.customer
GROUP BY c.id, c.shipped

This generates TWO lines for each customer - one with SUM(amount) of SHIPPED sales and one with SUM(amount) of NOT SHIPPED sales.

However I would need both SUM of amounts WHERE s.shipped=1 and a SUM of amounts where s.shipped=0 IN THE SAME QUERY. An example to clarify this even more:

If customer 1001 has these sales in the database:

id | dt | customer | amount | shipped
1 | 00-00-0000 | 1001 | 12.00 | 1
2 | 00-00-0000 | 1001 | 13.00 | 0
3 | 00-00-0000 | 1001 | 24.00 | 0
4 | 00-00-0000 | 1001 | 33.00 | 1

The qurey should return:

Customer ID | name | address | 82.00 | 45.00

Note that 82.00 is SUM of ALL sales and 45.00 is SUM of those sales, which have shipped=1

If I use the query above I get two lines:
Customer ID | name | address | 45.00
Customer ID | name | address | 37.00

(37.00 is SUM of amounts where shipped=0) which is not OK.

I hope you understand what I mean.

Can this be done?

Thanks!

 

[cLFlaVA]

Hmm, I can't test this, since I'm not on my MySQL-configured PC, but try this:

[tt]
SELECT c.id
, c.name
, c.address
, SUM(s.amount) as Total
, SUM(CASE WHEN s.shipped = 1 THEN s.amount ELSE 0 END) as ShipAmtTotal
FROM customers c join sales s
ON c.id = s.customer
GROUP BY c.id, c.name, c.address
[/tt]

*cLFlaVA
----------------------------
Breaking the habit...

 

[c4n]

Thank you VERY VERY much! This is exactly what I need.

Best regards,

c4n

 

[c4n]

cLFlaVA,

You were great help so far and I really appreachiate that. I will dare to ask you for a bit more help - I am trying to JOIN another (third) table in the query but when I do the SUM results are wrong (even those which work with the above query).

I also have a table CLICKS
customer | dt | number

customer ... customer ID number, the same as "id" in table CUSTOMERS and "customer" in table SALES
dt ... date (00-00-0000)
number ... number of clicks this day


Example:
customer | dt | number
1001 | 01-01-2004 | 20
1001 | 02-01-2004 | 18
1001 | 03-01-2004 | 13


Now I would like to include a SUM of number in CLICKS table in the query. I tried many variations of queries based on the above model, for example:

SELECT c.id
, c.name
, c.address
, SUM(s.amount) as Total
, SUM(CASE WHEN s.shipped = 1 THEN s.amount ELSE 0 END) as ShipAmtTotal
, SUM (k.number)
FROM customers c JOIN sales s
ON c.id = s.customer
JOIN clicks k ON c.id = k.customer
GROUP BY c.id, c.name, c.address


But these queries take long to execute (I'm talking about seconds) and the SUMs are WAY too large. Not just the new sum (k.number), all sums are much too large.

So this is the final version of the query I would like to return:

Customer ID | name | address | 82.00 | 45.00 | 51

51 = sum of all clicks per customer, no matter what the date was

What I get is something like

1001 | John Doe | address | 564456.4564 | 32145.1213 | 5463

(SUMs way too large)

If you can point me in the right direction that would be great.

I see you are an expert with SQL queries, can you recommend any good books/tutorials for advanced SQL queries?

Thanks a million!