Let's say i have two tables

Customer
---
Id Name
1  Foo
2  Bar

and

CustomerPurchase
---
CustomerId, Amount, AmountVAT, Accountable(bit)
1           10      11         1
1           20      22         0
2           5       6          0
2           2       3          0

I need a single record for every joined and grouped Customer and CustomerPurchase group. Every record would contain

1. columns from table Customer
2. some aggregation functions like SUM
3. a 'calculated' column. For example difference of other columns
4. result of subquery to CustomerPurchase table

An example of result i would like to get

CustomerPurchases
---
Name Total TotalVAT VAT TotalAccountable
Foo  30    33       3   10
Bar  7     9        2   0

I was able to get a single row only by grouping by all the common columns, which i dont think is the right way to do. Plus i have no idea how to do the 'VAT' column and 'TotalAccountable' column, which filters out only certain rows of CustomerPurchase, and then runs some kind of aggregate function on the result. Following example doesn't work ofc but i wanted to show what i would like to achieve

select C.Name,
       SUM(CP.Amount) as 'Total',
       SUM(CP.AmountVAT) as 'TotalVAT',
       diff? as 'VAT',
       subquery? as 'TotalAccountable'
from Customer C
inner join CustomerPurchase CR
on C.Id = CR.CustomerId
group by C.Id

CodePudding user response：

I would suggest you just need the follow slight changes to your query. I would also consider for clarity, if you can, to use the terms net and gross which is typical for prices excluding and including VAT.

select c.[Name],
    Sum(cp.Amount) as Total,
    Sum(cp.AmountVAT) as TotalVAT,
    Sum(cp.AmountVAT) - Sum(CP.Amount) as VAT,
    Sum(case when cp.Accountable = 1 then cp.Amount end) as TotalAccountable
from Customer c
join CustomerPurchase cp on cp.CustomerId = c.Id
group by c.[Name];