CODEWITHSUNDEEP
snowflake-cloud-data-platform
Dgbow
Dgbow

Reputation: 85

Summing Distinct values

I have this code, and I want to sum only the distinct values from the "TARGET_QUIKPLANSur.PLAN" table. But I don't know what I am doing wrong.

SELECT Distinct TARGET_QUIKPLANSur.PLAN, TARGET_QUIKPLANSur.FORM, Sum(TARGET_NEWQUIKVALFSurDec.MANNLZD) AS PREMIUM

from TARGET_QUIKPLANSur Left JOIN TARGET_NEWQUIKVALFSurDec ON TARGET_QUIKPLANSur.PLAN=TARGET_NEWQUIKVALFSurDec.NPLAN

GROUP BY TARGET_QUIKPLANSur.FORM, TARGET_QUIKPLANSur.PLAN

HAVING(((TARGET_QUIKPLANSur.FORM)='LN-5350') and TARGET_QUIKPLANSur.PLAN = 'N06000')

Upvotes: 0

Views: 170

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26088

we formatting your SQL so it's more readable:

SELECT DISTINCT q.plan
    ,q.form
    ,SUM(n.mannlzd) AS premium
FROM target_quikplansur AS q
LEFT JOIN target_newquikvalfsurdec AS n
    ON q.plan = n.nplan
GROUP BY q.form, q.plan
HAVING q.form='LN-5350' and q.plan = 'N06000'

One guess to what

I want to sum only the distinct values from the "TARGET_QUIKPLANSur.PLAN" table"

means, could be you have many rows in TARGET_QUIKPLANSur and you only want them to join once to your target_newquikvalfsurdec table, thus currently if you had two rows in target_newquikvalfsurdec with value 10 you want 20 as you answer, but if you have 2+ duplicate rows in TARGET_QUIKPLANSur you are getting something like 40

In that case you should deduplicate your TARGET_QUIKPLANSur values before joining to them

SELECT q.plan
    ,q.form
    ,SUM(n.mannlzd) AS premium
FOM (
    SELECT DISTINCT plan, form
    FROM target_quikplansur
) AS q
LEFT JOIN target_newquikvalfsurdec AS n
    ON q.plan = n.nplan
WHERE q.form='LN-5350' and q.plan = 'N06000'

Another way your question:

I want to sum only the distinct values from the "TARGET_QUIKPLANSur.PLAN" table"

could be read, is you have some rows with duplicates and you only want the rows with no duplicates. Thus for the data

form         plan      
'LN-5350',   'N06000'
'LN-5350',   'N06000'
'LN-5350',   'N06001'
'LN-5350',   'N06002'

you want only the rows 'N06001', & 'N06002' as they don't have duplicates. I suspect this is not what your wanting, but if it was you would use COUNT of the rows, and HAVING to filter like.

SELECT q.plan
    ,q.form
    ,SUM(n.mannlzd) AS premium
FOM (
    SELECT plan, form
    FROM target_quikplansur
    GROUP BY 1,2
    HAVING COUNT(*) = 1
) AS q
LEFT JOIN target_newquikvalfsurdec AS n
    ON q.plan = n.nplan
WHERE q.form='LN-5350' and q.plan = 'N06000'

OR another way to look at it, would be that you want the first row based on some other value, thus with:

form         plan      other
'LN-5350',   'N06000'  1
'LN-5350',   'N06000'  2
'LN-5350',   'N06001'  1
'LN-5350',   'N06002'  1

you might want the highest other value thus with a QUALIFY and ROW_NUMBER you can filter the values

SELECT q.plan
    ,q.form
    ,SUM(n.mannlzd) AS premium
FOM (
    SELECT plan, form
    FROM target_quikplansur
    QUALIFY ROW_NUMBER() OVER (PARTITION BY plan, form ORDER BY other DESC) = 1
) AS q
LEFT JOIN target_newquikvalfsurdec AS n
    ON q.plan = n.nplan
WHERE q.form='LN-5350' and q.plan = 'N06000'

to limit to these rows:

'LN-5350',   'N06000'  2
'LN-5350',   'N06001'  1
'LN-5350',   'N06002'  1

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10154

How about this?

SELECT PLAN, FORM, SUM(MANNLZD) FROM
(SELECT Distinct TARGET_QUIKPLANSur.PLAN, TARGET_QUIKPLANSur.FORM, TARGET_NEWQUIKVALFSurDec.MANNLZD AS PREMIUM
from TARGET_QUIKPLANSur Left JOIN TARGET_NEWQUIKVALFSurDec ON ARGET_QUIKPLANSur.PLAN=TARGET_NEWQUIKVALFSurDec.NPLAN )
GROUP BY FORM, PLAN
HAVING((FORM='LN-5350') and PLAN = 'N06000')

Filtering before grouping would be better:

SELECT PLAN, FORM, SUM(MANNLZD) FROM
(SELECT Distinct TARGET_QUIKPLANSur.PLAN, TARGET_QUIKPLANSur.FORM, TARGET_NEWQUIKVALFSurDec.MANNLZD AS PREMIUM
from TARGET_QUIKPLANSur Left JOIN TARGET_NEWQUIKVALFSurDec ON ARGET_QUIKPLANSur.PLAN=TARGET_NEWQUIKVALFSurDec.NPLAN 
WHERE FORM='LN-5350' and PLAN = 'N06000')
GROUP BY FORM, PLAN;

Upvotes: 1

Related Questions