CODEWITHSUNDEEP
sqldistinct
socool111
socool111

Reputation: 113

Using Distinct as a parameter

SELECT ORIGIN_NAME, SUM(NET_WEIGHT),
CASE WHEN ORIGIN_NAME IS NULL THEN 'Basket' ELSE ORIGIN_NAME END AS ORIGIN
FROM VW_PURCHASE_ORDER
GROUP BY ORIGIN_NAME

I have the above query, which I use in a pie chart.

However, because the information that this query grabs from has duplicate records I need to take the above query: (although I'm sure that the WHERE is wrong, because it lacks the appropriate expressions to finish the clause)

WHERE DISTINCT PO_PREFIX||PO_NUMBER||PO_SUFFIX

so as I do not double count the weights in the duplicate records

I've tried sub-queries, but I am currently a pretty big novice in SQL programming, and I don't think i did them correctly (can a SELECT DISTINCT column be used as a sub query?).

Upvotes: 2

Views: 3892

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

Lets break the operations into steps,

Step 1. Find all distinct rows:

SELECT DISTINCT 
   PO_PREFIX, PO_NUMBER, PO_SUFFIX, NET_WEIGHT,
   COALESCE(ORIGIN_NAME, 'Basket') AS ORIGIN
FROM 
   VW_PURCHASE_ORDER ;

Step 2: Group by origin, using the above as a derived table (named tmp): 

SELECT
   ORIGIN, SUM(NET_WEIGHT) AS SUM_NET_WEIGHT
FROM
   ( SELECT DISTINCT 
        PO_PREFIX, PO_NUMBER, PO_SUFFIX, NET_WEIGHT,
        COALESCE(ORIGIN_NAME, 'Basket') AS ORIGIN
     FROM 
        VW_PURCHASE_ORDER 
   ) tmp
GROUP BY
   ORIGIN ;

Upvotes: 5

mathieurip
mathieurip

Reputation: 537

I think what you want is to first select all the distinct rows and then make your group by. So this query might do what you need:

SELECT ORIGIN, SUM(NET_WEIGHT)
FROM (
SELECT DISTINCT 
* ,
CASE WHEN ORIGIN_NAME IS NULL THEN 'Basket' ELSE ORIGIN_NAME END AS ORIGIN
FROM VW_PURCHASE_ORDER) AS QUERY
GROUP BY ORIGIN

Upvotes: 0

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

I think this will somewhat help you

SELECT TOP 1 ORIGIN_NAME, SUM(NET_WEIGHT),
CASE WHEN ORIGIN_NAME IS NULL THEN 'Basket' ELSE ORIGIN_NAME END AS ORIGIN
FROM VW_PURCHASE_ORDER
GROUP BY ORIGIN_NAME

Upvotes: 0

Related Questions