'How do I do a sum per id?

SELECT distinct
 A.PROPOLN, C.LIFCLNTNO, A.PROSASORG, sum (A.PROSASORG) as sum
     FROM [FPRODUCTPF] A 
        join [FNBREQCPF] B on (B.IQCPLN=A.PROPOLN)
        join [FLIFERATPF] C on (C.LIFPOLN=A.PROPOLN and C.LIFPRDCNT=A.PROPRDCNT and C.LIFBNFCNT=A.PROBNFCNT)
           where C.LIFCLNTNO='2012042830507' and A.PROSASORG>0 and A.PROPRDSTS='10' and 
               A.PRORECSTS='1' and A.PROBNFLVL='M' and B.IQCODE='B10000' and B.IQAPDAT>20180101 
                        group by C.LIFCLNTNO, A.PROPOLN, A.PROSASORG

This does not sum correctly, it returns two lines instead of one:

  PROPOLN     LIFCLNTNO         PROSASORG     sum

1 209814572 2012042830507   3881236     147486968

2 209814572 2012042830507   15461074    463832220
sqlsql-server


Solution 1:[1]

You are seeing two rows because A.PROSASORG has two different values for the "C.LIFCLNTNO, A.PROPOLN" grouping.

i.e.

C.LIFCLNTNO, A.PROPOLN, A.PROSASORG together give you two unique rows.

If you want a single row for C.LIFCLNTNO, A.PROPOLN, then you may want to use an aggregate on A.PROSASORG as well.

Solution 2:[2]

Your entire query is being filtered on your "C" table by the one LifClntNo, so you can leave that out of your group by and just have it as a MAX() value in your select since it will always be the same value.

As for you summing the PROSASORG column via comment from other answer, just sum it. Hour column names are not evidently clear for purpose, so I dont know if its just a number, a quantity, or whatever. You might want to just pull that column out of your query completely if you want based on a single product id.

For performance, I would suggest the following indexes on

Table        Index
FPRODUCTPF   ( PROPRDSTS, PRORECSTS, PROBNFLVL, PROPOLN )
FNBREQCPF    ( IQCODE, IQCPLN, IQAPDAT )
FLIFERATPF   ( LIFPOLN, LIFPRDCNT, LIFBNFCNT, LIFCLNTNO )

I have rewritten your query to put the corresponding JOIN components to the same as the table they are based on vs all in the where clause.

SELECT 
        P.PROPOLN, 
        max( L.LIFCLNTNO ) LIFCLNTNO, 
        sum (P.PROSASORG) as sum
    FROM 
        [FPRODUCTPF] P
            join [FNBREQCPF] N 
                on N.IQCODE = 'B10000'
                and P.PROPOLN = N.IQCPLN
                and N.IQAPDAT > 20180101 

            join [FLIFERATPF] L 
                on L.LIFCLNTNO='2012042830507' 
                and P.PROPOLN = L.LIFPOLN 
                and P.PROPRDCNT = L.LIFPRDCNT 
                and P.PROBNFCNT = L.LIFBNFCNT
    where 
            P.PROPRDSTS = '10' 
        and P.PRORECSTS = '1' 
        and P.PROBNFLVL = 'M' 
        and P.PROSASORG > 0 
    group by 
        P.PROPOLN

Now, one additional issue you will PROBABLY be running into. You are doing a query with multiple joins, and it appears that there will be multiple records in EACH of your FNBREQCPF and FLIFERATPF tables for the same FPRODUCTPF entry. If you, you will be getting a Cartesian result as the PROSASORG value will be counted for each instance combination in the two other tables.

Ex: FProductPF has ID = X with a Prosasorg value of 3

FNBreQCPF has matching records of Y1 and Y2

FLIFERATPF has matching records of Z1, Z2 and Z3.

So now your total will be equal to 3 times 6 = 18. If you look at the combinations, Y1:Z1, Y1:Z2, Y1:Z3 AND Y2:Z1, Y2:Z2, Y2:Z3 giving your 6 entries that qualify, times the original value of 3, thus bloating your numbers -- IF such multiple records may exist in each respective table. Now, imagine if your tables have 30 and 40 matching instances respectively, you have just bloated your totals by 1200 times.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 gaurav
Solution 2 DRapp

Related Questions