英文:
SQL Sum by Group
问题
我有一个像这样的发票表：
| 组别 | 客户 | 发票金额 |
|---|---|---|
| 组别 1 | 约翰 | 250 |
| 组别 1 | 约翰 | 250 |
| 组别 2 | 黛布拉 | 250 |
我想要将报告显示为：
| 组别 | 客户 | 发票金额 |
|---|---|---|
| 组别 1 | 约翰 | 500 |
| 组别 2 | 黛布拉 | 250 |
基本上我想要按组别求和。
为了实现这一点，我尝试了以下代码：
select dbo.Clients.Groups,
SUM(dbo.ClientInvoices.Amount)
from dbo.Clients
INNER JOIN dbo.ClientInvoices ON dbo.Clients.Groups = dbo.Clients.Groups
有人能否指导如何实现这个目标？
英文:
I have a invoices table like this:
| Group | Client | Invoice Amount |
|---|---|---|
| Group 1 | John | 250 |
| Group 1 | John | 250 |
| Group 2 | Debra | 250 |
I would like to display the report as:
| Group | Client | Invoice Amount |
|---|---|---|
| Group 1 | John | 500 |
| Group 2 | Debra | 250 |
Essentially I'd like to sum by group.
To achieve this, the code that I've tried is:
select dbo.Clients.Groups,
dbo.ClientInvoices.Amount
from dbo.Clients
INNER JOIN dbo.ClientInvoices ON dbo.Clients.Groups = dbo.Clients.Groups
Is anyone able to advise how to achieve this?
答案1
得分: 1
你可以使用 [SUM][1] 和 [GROUP BY][2]
这是一个示例（你可能需要根据你的确切用例更改列名）
```sql
SELECT Clients.Group,
Clients.Client,
SUM(ClientInvoices.Amount) AS Amount
FROM Clients
LEFT JOIN ClientInvoices ON Clients.Group = ClientInvoices.Group
GROUP BY Clients.Group, Clients.Client
<details>
<summary>英文:</summary>
You can use [SUM][1] and [GROUP BY][2]
Here's an example (you might need to change the column names for your exact use case)
```sql
SELECT Clients.Group,
Clients.Client,
SUM(ClientInvoices.Amount) AS Amount
FROM Clients
LEFT JOIN ClientInvoices ON Clients.Group = ClientInvoices.Group
GROUP BY Clients.Group, Clients.Client
答案2
得分: 0
以下是翻译好的内容：
您可以尝试使用分组或窗口函数来解决您的问题
select
a.[Group]
,a.Client
,a.Amount as [Invoice Amount]
from (
SELECT c.[Group],
c.Client,
sum(ci.Amount) over(
partition by
c.[Group]
, c.Client
) as Amount,
row_number() over( partition by c.[Group], c.Client order by c.[Group], c.Client) as rw
FROM Clients c
LEFT JOIN ClientInvoices ci ON c.[Group] = ci.[Group]
)a
where a.rw=1
您可以使用以下代码插入基本数据
drop table if exists Clients
drop table if exists ClientInvoices
create table Clients([Group] varchar(100),Client varchar(100))
create table ClientInvoices([Group] varchar(100),Amount int)
insert into Clients([Group],Client)
select 'Group 1','John'
union all select 'Group 1','John'
union all select 'Group 2','Debra'
insert into ClientInvoices([Group],Amount)
select 'Group 1',250
union all select 'Group 2', 250
英文:
You can try using a group by or Window function for solve your problem
select
a.[Group]
,a.Client
,a.Amount as [Invoice Amount]
from (
SELECT c.[Group],
c.Client,
sum(ci.Amount) over(
partition by
c.[Group]
, c.Client
) as Amount,
row_number() over( partition by c.[Group], c.Client order by c.[Group], c.Client) as rw
FROM Clients c
LEFT JOIN ClientInvoices ci ON c.[Group] = ci.[Group]
)a
where a.rw=1
You can to insert the basic data with the following codes
drop table if exists Clients
drop table if exists ClientInvoices
create table Clients([Group] varchar(100),Client varchar(100))
create table ClientInvoices([Group] varchar(100),Amount int)
insert into Clients([Group],Client)
select 'Group 1','John'
union all select 'Group 1','John'
union all select 'Group 2','Debra'
insert into ClientInvoices([Group],Amount)
select 'Group 1',250
union all select 'Group 2', 250
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论