SQL Server SQL Sum by Group

qf9go6mv 于 2023-04-19 发布在其他

关注(0)|答案(2)|浏览(147)

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?

sql-server

来源：https://stackoverflow.com/questions/76031659/sql-sum-by-group

2条答案

按热度按时间

wfsdck301#

You can use SUM and GROUP BY

Here's an example (you might need to change the column names for your exact use case)

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

赞(0）回复(0）举报 2023-04-19

epggiuax2#

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

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server SQL Sum by Group

2条答案

相关问题

热门标签

最新问答