将新输入的行与另一个列值相乘,然后在SQL中找到总和 [英] Multiply newly entered row with another column value and find Total Sum in SQL
问题描述
我这里有4个表,我需要将一个表中新输入的行值与另一行相乘,并使用CustomerId
查找总和:
I have 4 tables here, I need to multiply newly entered row value in a table with another row and find the total sum using CustomerId
:
CustomerTable :
CustomerId Name EmailId
-------------------------
1 Paul r@r.com
2 John J@j.com
LoyaltyPointTable :
LoyaltyPointsId LoyaltyType Points
---------------------------------------
1 Registration 10
2 Loginstatus 1
3 Downloading 10
4 Redemming 1
5 Sharing 20
6 Refer 10
忠诚度详细信息表:
LoyaltyDetailsId LoyaltyPointsId CustomerId Dates
-------------------------------------------------
1 1 1 2015-01-22
2 2 1 2015-01-22
3 3 2 2015-01-22
4 3 1 2015-01-22
5 4 1 2015-01-22
6 4 1 2015-01-24
7 5 1 2015-01-24
此查询对于每个LoyaltyType
SELECT
LoayaltyPointsTable.LoyaltyType,
COUNT(CustomerTable.CustomerId) AS UserActions,
SUM(LoayaltyPointsTable.Points) AS TotalPoints
FROM
LoayaltyPointsTable
JOIN
LoyaltyDetailsTable ON LoayaltyPointsTable.LoyaltyPointsId = LoyaltyDetailsTable.LoyaltyPointsId
JOIN
CustomerTable ON CustomerTable.CustomerId = LoyaltyDetailsTable.CustomerId
WHERE
CustomerTable.CustomerId = 1
GROUP BY
LoyaltyDetailsTable.CustomerId ,LoayaltyPointsTable.LoyaltyType
在RedeemPointsTable
下面的
是与与LoyaltyPointTable
中的行兑换有关的创建的:
below RedeemPointsTable
is created with relation to row redeeming in LoyaltyPointTable
:
RedeemPointsTable :
RedeemPointsId CustomerId ShopName BillNo Amount
------------------------------------------------
1 1 Mall x 4757 100
3 1 Mall y SH43 50
4 1 Mall x 7743 10
6 1 Mall x s34a 60
我期望的是在计算总和之前,我希望将LoyaltyPointTable
中Redeeming
中的Amount
总和(100+50+10+60) * 1
列与每个CustomerId
What I am expecting is before calculating the total sum, I want column Amount
sum (100+50+10+60) * 1
in Redeeming
in LoyaltyPointTable
to be added with total points for each CustomerId
预期输出
LoyaltyType UserActions TotalPoints
-------------------------------------
Downloading 1 10
Loginstatus 1 1
Redemming 4 (100+50+10+60)*1(here using Amount in RedeemPointsTable)
Refer 1 10
Registration 1 10
Sharing 1 20
用户操作计数为4,它基于他在RedeemPointsTable
User actions count is 4, it is based on the Amount
he entered in RedeemPointsTable
我需要在RedeemPointsTable
中添加外键列时进行更改,还是可以指出我的错误?
Should I need to make changes in adding a foreign key column in RedeemPointsTable
or can you point out my mistake?
任何帮助都会很棒.
推荐答案
这是返回所需结果的查询:
This is the query which returns desired result:
SELECT
LoyaltyPointTable.LoyaltyType,
CASE
WHEN LoyaltyPointTable.LoyaltyPointsId=4 THEN (SELECT COUNT(amount) FROM RedeemPointsTable where CustomerId=1)
ELSE COUNT(CustomerTable.CustomerId)
END as UserActions,
CASE
WHEN LoyaltyPointTable.LoyaltyPointsId=4 THEN (SELECT SUM(amount) FROM RedeemPointsTable where CustomerId=1)*Points
ELSE SUM(LoyaltyPointTable.Points)
END as TotalPoints
FROM
LoyaltyPointTable
JOIN
LoyaltyDetailsTable ON LoyaltyPointTable.LoyaltyPointsId = LoyaltyDetailsTable.LoyaltyPointsId
JOIN
CustomerTable ON CustomerTable.CustomerId = LoyaltyDetailsTable.CustomerId
WHERE
CustomerTable.CustomerId = 1
GROUP BY
LoyaltyDetailsTable.CustomerId ,LoyaltyPointTable.LoyaltyType
您可以在此处
这篇关于将新输入的行与另一个列值相乘,然后在SQL中找到总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!