group by statement complex [英] group by statement complex

问题描述

嗨
我有以下表格：



人员表：

id |姓名

1 |艾伯特

2 |山姆

3 | adel

...



交易表：

tID | pID |价格

1 | 1 | 1000

2 | 1 | 1200

3 | 3 | 780

...



我要一份这样的报告：



名称| sumOfPrice

albert | 2200

sam | 0

adel | 780



i尝试在交易表上使用group by，但是此声明给我一个如下报告，sam没有记录，什么是解决方案？！



名称| sumOfPrice

albert | 2200

adel | 780



谢谢



SQL查询添加

Hi I have bellow tables:

Person Table:
id | name
1 | albert
2 | sam
3 | adel
...

transaction table:
tID | pID | price
1 | 1 | 1000
2 | 1 | 1200
3 | 3 | 780
...

I want a report like this:

name | sumOfPrice
albert | 2200
sam | 0
adel | 780

i try with "group by" on "transaction table" but this statement give me a report like below and "sam" has no record, what is solution?!

name | sumOfPrice
albert | 2200
adel | 780

thanks

[edit] SQL query added

SELECT Person.name, Sum(transaction.price) AS SumOfPrice
FROM Person RIGHT JOIN transaction ON Person.id = transaction.pID
GROUP BY Person.name
ORDER BY Person.name

[/ edit]

[/edit]

推荐答案

您可以尝试反转您的联接：

You could try with inversing your join:

SELECT Person.name, Sum(transaction.price) AS SumOfPrice
FROM Person LEFT JOIN transaction ON Person.id = transaction.pID
GROUP BY Person.name
ORDER BY Person.name

并查看它是否符合您的需求。

and see if it fits your needs.

您可以尝试： -

You can try:-

select Person.Name,sum(transaction.Price) as SumOfPrice from Person left outer join transaction 
on Person.ID=transaction.Pid group by Person.Name order by Person.Name

这里 sam SumOfPrice 将 null

这篇关于group by statement complex的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！