CODEWITHSUNDEEP
sqlpostgresql
Beginner
Beginner

Reputation: 1740

query to get the SUM

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.

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

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

Upvotes: 1

Related Questions