query to get the SUM

Question

Supposed I have a data of

code_table

code_id | code_no | stats | 2 60 22A3 3 60 22A3

value_table

value_no | amount_value_one | amount_value_two | amount_diff | code_no | sample_no | code_id 1 1200.00 400.00 800.00 60 90 2 1 600.00 200.00 400.00 60 100 3 1 1800.00 600.00 1200.00 60 110 2 2 1200.00 1200.00 0.00 60 110 2 2 800.00 600.00 200.00 60 90 2 2 400.00 0.00 400.00 60 100 3

What I want to happen is to get all the SUM of amount_value_two and just retain the first amount_value_one which has the value_no = 1

the output can be conclude as

amount_value_one | SUM_of_amount_value_two | amount_diff | sample_no 1200.00 1000.00 200.00 90 600.00 200.00 400.00 100 1800.00 1.800.00 0.00 110

so far i have this following query

SELECT SUM(p.amount_value_one) as value_one, SUM(p.amount_value_two) as value_two, SUM(p.amount_diff) as amount_diff, p.sample_no as sampleNo FROM value_table p INNER JOIN code_table On code_table.code_no = p.code_no WHERE code_table.code_id = p.code_id AND code_table.stats = '22A3' GROUP BY p.sample_no

the query above that I used is wrong because it gets the sum of both p.amount_value_one and p.amount_diff

its just a test query because i cant imagine what would the query will look like.

SQL tables represent unordered sets. There is no such thing as the "first" value, unless a column specifies the ordering. — Gordon Linoff
– Gordon Linoff, Commented Apr 17, 2020 at 10:36

Gordon Linoff · Accepted Answer · 2020-04-17 12:35:08Z

Assuming that you have a column that specifies the ordering, then you can use that to figure out the "first" row. Then use conditional aggregation:

SELECT SUM(CASE WHEN seqnum = 1 THEN p.amount_value_one END) as value_one, SUM(p.amount_value_two) as value_two, SUM(p.amount_diff) as amount_diff, p.sample_no as sampleNo FROM (SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.sample_no ORDER BY <ordering column>) as seqnum FROM value_table p ) p JOIN code_table ct ON ct.code_no = p.code_no AND ct.code_id = p.code_id WHERE ct.stats = '22A3' GROUP BY p.sample_no

Collectives™ on Stack Overflow

query to get the SUM

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related