CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jorame
jorame

Reputation: 2207

Distinct query with sum operator not working?

I have a question, I have this query where I'm supposed to pull the totals group by PIX_SKU. My issue here is that is doubling the quantities, any help will be appreciate it.

SELECT DISTINCT(A2.PIX_SKU), A2.PIX_DATE, SUM(A1.ID_QTY) AS TOTAL
FROM IDCASE00_EXTRACT A1,PX_CLEAN A2
WHERE A1.ID_SKU = A2.PIX_SKU
GROUP BY A2.PIX_SKU, A2.PIX_DATE

Here is some data for table IDCASE00_EXTRACT

   id_sku                  id_ty       
301110  39  8               3266         
301110  39  8               1788         
301112  90  1               4           
301185  20  6               6580        
301185  20  6               22028

Here is some data for table PX_CLEAN

  pix_sku           pix_date
301110  39  8       20120203
301112  90  1       20120204
301185  20  6       20120320
301110  39  8       20120301
301185  20  6       20120209

Upvotes: 0

Views: 629

Answers (3)

Ido Gal
Ido Gal

Reputation: 527

This query:

SELECT pix_sku, SUM(id_qty) AS QuantityTotal
FROM @PX_CLEAN AS P
    JOIN @IDCASE00_EXTRACT AS Q ON (Q.id_sku = P.pix_sku)
GROUP BY pix_sku

will get those results:

  • SKU | TOTAL
  • 301110 39 8 | 10108
  • 301112 90 1 | 4
  • 301185 20 6 | 57216

For each SKU, u have a total.

What exactly do you want to get from the pix_date? You have only ONE sku when you aggregate, but there are still several dates for each single SKU. What are u trying to get? what do you want to know?

You should stop thinking about the DISTINCT function. This function only removes the exactly same rows from the query results. It helps only if u got the results that u want - but there are multiple lines which are the same. The query engine wont "understand" your wishes and just return 1 row for each SKU with distinct, that can be achieved only by aggregation (group by).

Upvotes: 0

Ido Gal
Ido Gal

Reputation: 527

This query should get you the total for each SKU:

SELECT Q.ID_SKU, SUM(Q.ID_qty) AS TOTAL
FROM   IDCASE00_EXTRACT AS Q
GROUP BY Q.ID_SKU

What do you intend to do with the pix_date? What use does this data have?

The only other information which you can get that I can think of is:

SELECT T.ID_SKU, T.TOTAL, PX.pix_date
FROM
     (  
     SELECT Q.ID_SKU, SUM(Q.ID_qty) AS TOTAL
     FROM   IDCASE00_EXTRACT AS Q
     GROUP BY Q.ID_SKU
     ) 
     AS T JOIN PX_CLEAN AS PX ON (T.ID_SKU = PX.pix_sku)

Anyway, you have to clarify what information you want to retrieve.

Upvotes: 2

philf2b
philf2b

Reputation: 201

Your join is the problem ...

In IDCASE00_EXTRACT, you have two rows where id_sku = 301110 39 8.

In PX_CLEAN, you also have two rows where pix_sku = 301110 39 8.

So when you join on id_sku = pix_sku you're generating 4 rows (2 * 2).

So the match that joins the two tables is not currently unique - is there a date in IDCASE00_EXTRACT that should match the pix_date in PX_CLEAN?

Upvotes: 5

Related Questions