SQL Server query for calculating sum

Question

I am trying to write a SQL query for calculating sum without success.

Let's say that we have:

table A with columns id and type
table B with columns id, a_id (relation to table A) and amount

I succeed to calculate number of records by type like in the following example:

SELECT DISTINCT
    type,
    COUNT(A.id) OVER (PARTITION BY type) AS numOfRecords
FROM A;

How to calculate sum of amounts also per type (to sum up all amounts from table B for all distinct types in A)?

If you want to sum something, what's wrong with SUM? Why are you using DISTINCT and OVER when clearly what you really want is a GROUP BY clause? — Thom A
– Thom A ♦, Commented Feb 2, 2021 at 16:42

Gordon Linoff · Accepted Answer · 2021-02-02 16:43:24Z

4

Your query would normally be written as:

select type, count(*) as num_records
from A
group by type;

Then, you can incorporate b as:

select a.type, count(*) as num_records, sum(b.amount)
from A left join
     (select a_id, sum(amount) as amount
      from b
      group by a_id
     ) b
     on b.a_id = a.id
group by a.type;

You can also join and aggregate without a subquery, but this will throw off the count. To fix that, you can use count(distinct):

select a.type, count(distinct a.id) as num_records, sum(b.amount)
from A left join
     from b
     on b.a_id = a.id
group by a.type;

answered Feb 2, 2021 at 16:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server query for calculating sum

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related