使用SUM查询每列的MYSQL DB(Query MYSQL DB with SUM for each column)

我有一个带有6个基本表的数据库，我有一个问题，显示总和正确的简单摘要。这是我的精简版设置：

table: customer_info --------------------------------------------------------- custID | store | custName 1 | bayport | renee 2 | bayport | april 3 | plainfield | john 4 | bayport | tree table: deals --------------------------------------------------------- dealID | custID | dealDate | empName 1 | 1 | 2013-04-01 | shak 2 | 2 | 2013-04-01 | shak 3 | 3 | 2013-04-04 | jen 4 | 4 | 2013-04-05 | shak table: phones --------------------------------------------------------- phoneID | dealID | instore | online 1 | 1 | 1 | 0 2 | 1 | 0 | 1 3 | 2 | 1 | 0 4 | 3 | 0 | 1 5 | 3 | 1 | 0 6 | 3 | 1 | 0 7 | 4 | 0 | 1 table: accessory --------------------------------------------------------- accID | dealID | acName | price 1 | 1 | lanyard | 10 2 | 2 | pen | 5 3 | 4 | acc | 2 3 | 4 | blip | 15 table: others ----------------------------------------------------------- otherID | dealID | otName | otPrice 1 | 2 | other | 250 2 | 2 | other2 | 100 table:payments ----------------------------------------------------------- paymentID | dealID | cash | credit 1 | 1 | 10 | 0 2 | 2 | 0 | 355 3 | 3 | 0 | 0 4 | 4 | 17 | 0

以下是我需要的总结：

Date | empName | instore | online | credit | cash | total | accTotal | otherTotal | countOthers 2013-04-01 | shak | 2 | 1 | 355 | 10 | 360 | 15 | 350 | 2 2013-04-04 | jen | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 2013-04-05 | shak | 0 | 0 | 0 | 17 | 17 | 17 | 0 | 0

店内，在线，信贷，现金，总额，accTotal，otherTotal是各自列的总金额，而countOthers是“其他”完成的数量的计数

这是我到目前为止所尝试的，但数量就像是遍布整个地方

select dealDate,empName, Sum(phone.instore) AS 'In-store', Sum(phone.online) As 'Online', sum(credit) AS 'Credit', sum(cash) AS 'Cash', sum(cash+credit) AS 'Total', sum(price) AS 'accTotal' sum(otprice) AS 'otherTotal' COUNT(otName) AS 'OthersCount' FROM customer_info JOIN deals LEFT join phones ON deals.dealID = phones.dealID LEFT JOIN payments ON deals.dealID = payments.dealID LEFT JOIN accessory ON deals.dealID = accessory.dealID LEFT JOIN others ON deals.dealID = others.dealID WHERE customer_info.custID = deals.custID GROUP BY deals.dealDate ORDER BY dealDate DESC

任何帮助，将不胜感激。结果可以是由dealDate或dealID组合，哪个更容易。非常感谢你。

I have a db with 6 basic tables, I am having problems showing the sum properly for a simple summary. Here is my stripped down table setup:

table: customer_info --------------------------------------------------------- custID | store | custName 1 | bayport | renee 2 | bayport | april 3 | plainfield | john 4 | bayport | tree table: deals --------------------------------------------------------- dealID | custID | dealDate | empName 1 | 1 | 2013-04-01 | shak 2 | 2 | 2013-04-01 | shak 3 | 3 | 2013-04-04 | jen 4 | 4 | 2013-04-05 | shak table: phones --------------------------------------------------------- phoneID | dealID | instore | online 1 | 1 | 1 | 0 2 | 1 | 0 | 1 3 | 2 | 1 | 0 4 | 3 | 0 | 1 5 | 3 | 1 | 0 6 | 3 | 1 | 0 7 | 4 | 0 | 1 table: accessory --------------------------------------------------------- accID | dealID | acName | price 1 | 1 | lanyard | 10 2 | 2 | pen | 5 3 | 4 | acc | 2 3 | 4 | blip | 15 table: others ----------------------------------------------------------- otherID | dealID | otName | otPrice 1 | 2 | other | 250 2 | 2 | other2 | 100 table:payments ----------------------------------------------------------- paymentID | dealID | cash | credit 1 | 1 | 10 | 0 2 | 2 | 0 | 355 3 | 3 | 0 | 0 4 | 4 | 17 | 0

Here is what I need my summary to look like:

Date | empName | instore | online | credit | cash | total | accTotal | otherTotal | countOthers 2013-04-01 | shak | 2 | 1 | 355 | 10 | 360 | 15 | 350 | 2 2013-04-04 | jen | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 2013-04-05 | shak | 0 | 0 | 0 | 17 | 17 | 17 | 0 | 0

WHERE instore, online,credit,cash,total,accTotal,otherTotal are total amounts of their respective columns while countOthers is a count of how many 'others' was done

Here is what I tried so far but the amount is like multiplying all over the place

select dealDate,empName, Sum(phone.instore) AS 'In-store', Sum(phone.online) As 'Online', sum(credit) AS 'Credit', sum(cash) AS 'Cash', sum(cash+credit) AS 'Total', sum(price) AS 'accTotal' sum(otprice) AS 'otherTotal' COUNT(otName) AS 'OthersCount' FROM customer_info JOIN deals LEFT join phones ON deals.dealID = phones.dealID LEFT JOIN payments ON deals.dealID = payments.dealID LEFT JOIN accessory ON deals.dealID = accessory.dealID LEFT JOIN others ON deals.dealID = others.dealID WHERE customer_info.custID = deals.custID GROUP BY deals.dealDate ORDER BY dealDate DESC

Any help would be appreciated. The result could be group by either dealDate or the dealID whichever is easier. Thank you very much.