JOIN and CASE duplicating Query results

Question

I am working with two tables within Teradata and I am trying to query every active item by store number, Total Days To Sell and total days within each store number. My current Query setup is providing two rows of data by SKU instead of supplying one row that shows the appropriate information.

Table 1: Shows class number, class name, status and store data.

Store   Item    Class Number    Class Name         Status
100     Apple      10            Red Fruit         Active
200     Apple      10            Red Fruit         Active
100     Banana     12            Yellow Fruit      Active
200     Banana     12            Yellow Fruit      Active
100     Pear       14            Green Fruit       Active
200     Pear       14            Green Fruit       Active
100     Beans      20            Green Vegetable   Discontinued
200     Beans      20            Green Vegetable   Active

Table 2: Shows Total days to sell by store and item

Store   Item    Total Days to sell
100     Apple         4
200     Apple         1
100     Banana        2
200     Banana        4
100     Pear          3
200     Pear          6
100     Beans        NULL
200     Beans         4

Table 3: Current Query results

Item  Class Number   Class Name  Total Days to sell  Store 100   Store 200
Apple     10         Red Fruit           5               4         NULL
Apple     10         Red Fruit           5              NULL        1
Banana    12         Yellow Fruit        6               2         NULL
Banana    12         Yellow Fruit        6              NULL        4
Pear      14         Green Fruit         9               3         NULL
Pear      14         Green Fruit         9              NULL        6
Beans     20         Green Vegetable     4              NULL        4

Below is How I am looking for the data to be organized:

Item    Class Number    Class Name  Total Days to sell  Store 100   Store 200
Apple     10             Red Fruit          5              4           1
Banana    12             Yellow Fruit       6              2           4
Pear      14             Green Fruit        9              3           6
Beans     20             Green Vegetable    4             NULL         4

Current Query:

SELECT DISTINCT     
    A.ITEM, 
    A.CLASS_NUMBER, 
    A.CLASS_NAME,   
    SUM(B.TOTAL_DAYS_TO_SELL),  
    CASE    
        WHEN B.STORE=100 THEN B.TOTAL_DAYS_TO_SELL
        ELSE NULL
        END AS STORE 100,
    CASE    
        WHEN B.STORE=200 THEN B.TOTAL_DAYS_TO_SELL
        ELSE NULL
        END AS STORE 200
FROM TABLE 1 A      
RIGHT JOIN TABLE 2 B        
ON B.ITEM=A.ITEM        
WHERE A.STATUS='ACTIVE'     
GROUP BY        
    A.ITEM, 
    A.CLASS_NUMBER, 
    A.CLASS_NAME,   
    STORE 100,  
    STORE 200   
ORDER BY        
    A.CLASS_NUMBER ASC; 

Please let me know if you have any questions regarding the information

Thanks!

Answer 1

I would write this as:

SELECT A.ITEM, A.CLASS_NUMBER, A.CLASS_NAME, SUM(B.TOTAL_DAYS_TO_SELL),  
       SUM(CASE WHEN B.STORE = 100 THEN B.TOTAL_DAYS_TO_SELL END) as STORE_100,
       SUM(CASE WHEN B.STORE = 200 THEN B.TOTAL_DAYS_TO_SELL END) as STORE_200
FROM TABLE1 A JOIN     
     TABLE2 B        
     ON B.ITEM = A.ITEM        
WHERE A.STATUS = 'ACTIVE'     
GROUP BY A.ITEM, A.CLASS_NUMBER, A.CLASS_NAME
ORDER BY A.CLASS_NUMBER ASC;

This is essentially Lamak's answer, but with more editorial:

• Don't use SELECT DISTINCT unless you really know what you are doing. It is almost never appropriate with GROUP BY.
• You have three columns that are not being aggregated; those should be the ones in the GROUP BY.
• Your RIGHT OUTER JOIN is being turned into an INNER JOIN by the WHERE clause. Just use INNER JOIN. Further, you are aggregating by columns in A. Do you really want aggregation columns that are NULL?
• The ELSE clause is not needed on the CASE, because the default is NULL.
• This version chooses SUM() for the aggregation. With one numeric value, SUM(), MIN(), and MAX() all return the same things. But, I think SUM() is safer in case you want to remove some aggregation keys.

Answer 2

First of all, no need for DISTINCT, since you are using GROUP BY.

For the result that you want, you need to use an aggregation function on the CASE expressions that you are using. From the data that you showed, you could use MIN, MAX or SUM (even AVG):

SELECT DISTINCT     
    A.ITEM, 
    A.CLASS_NUMBER, 
    A.CLASS_NAME,   
    SUM(B.TOTAL_DAYS_TO_SELL),  
    MIN(CASE    
            WHEN B.STORE=100 THEN B.TOTAL_DAYS_TO_SELL
            ELSE NULL
         END) AS STORE 100,
    MIN(CASE    
            WHEN B.STORE=200 THEN B.TOTAL_DAYS_TO_SELL
            ELSE NULL
        END) AS STORE 200
FROM TABLE 1 A      
RIGHT JOIN TABLE 2 B        
ON B.ITEM=A.ITEM        
WHERE A.STATUS='ACTIVE'     
GROUP BY        
    A.ITEM, 
    A.CLASS_NUMBER, 
    A.CLASS_NAME
ORDER BY        
    A.CLASS_NUMBER ASC;