使用SUM查询每列的MYSQL DB

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:

我有一个带有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    

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

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

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.

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

1 个解决方案

#1

I got it! I just sub-queried the sections that was multiplying my total.

我知道了!我刚刚查询了那些乘以我总数的部分。