CODEWITHSUNDEEP
sqloracle-databaseoracle10g
AT-2017
AT-2017

Reputation: 3149

Group By Creates Duplicate Rows

I am using Oracle sql to create a sample data GridView and run into a very basic issue. So here it's, I've to organize data month-wise, say no of employees in a month based on a status column. So status = 0; Jan1 and status > 0; Jan2. I am not elaborating anything else as it has already a built-in view and that's what I've to use to make it work. So here is the query that I am using and the sample output that works fine except one:

SELECT DISTINCT SYEAR, DEPT_NAME,

--Month-wise data - Starts
DECODE ( upper((MONTHNAMESHORT)), 'JAN', NVL((FirstLetter), 0), NULL) "JAN1" ,  
DECODE ( upper((MONTHNAMESHORT)), 'JAN', NVL((SecondLetter), 0), NULL) "JAN2",
DECODE ( upper((MONTHNAMESHORT)), 'FEB', NVL((FirstLetter), 0), NULL) "FEB1" ,  
DECODE ( upper((MONTHNAMESHORT)), 'FEB', NVL((SecondLetter), 0), NULL) "FEB2"
--Month-wise data - Ends

FROM

--Sub-query - starts
(SELECT DISTINCT VWWEBLETTERSTATUS2.SYEAR, MONTHRANK.MONTHNAMESHORT,VWWEBLETTERSTATUS2.DEPT_NAME,
  nvl(fnfirstletter(DEPT_NAME,upper(MONTHRANK.MONTHNAMESHORT),VWWEBLETTERSTATUS2.SYEAR),0) FirstLetter,
  nvl(fnSecondLetter(DEPT_NAME,upper(MONTHRANK.MONTHNAMESHORT),VWWEBLETTERSTATUS2.SYEAR),0) SecondLetter,MONTHRANK.RANK  
  FROM
MONTHRANK,VWWEBLETTERSTATUS2 where VWWEBLETTERSTATUS2.SYEAR = '2018' AND
  nvl(fnfirstletter(DEPT_NAME,upper(MONTHRANK.MONTHNAMESHORT),VWWEBLETTERSTATUS2.SYEAR), 0) <> 0 AND
  nvl(fnSecondLetter(DEPT_NAME,upper(MONTHRANK.MONTHNAMESHORT),VWWEBLETTERSTATUS2.SYEAR), 0) <> 0
order by DEPT_NAME, rank) q
--Sub-query - Ends

GROUP BY SYEAR, (MONTHNAMESHORT), DEPT_NAME; --Issue here - For the month-wise group by

Output

Year    Dept    Jan1 Jan2 Feb1 Feb2
2018    UNIT-I3 93   87             
2018    UNIT-I5 62   66
2018    QA      0    0      
2018    UNIT-I5           87   66

Here for the GROUP BY (MONTHNAMESHORT) clause, it creates duplicate rows for the department and that specific year. Say when Unit-I5 has data for both the months, it creates separate rows though it should be in a single row. Any way to overcome the issue keeping the same thing, just an alternate for the GROUP BY?

Update 1: Even tried this one, but didn't work

SUM(CASE WHEN Q.MONTHNAMESHORT = 'JAN' THEN Q.FirstLetter ELSE 0 END) "JAN1",
SUM(CASE WHEN Q.MONTHNAMESHORT = 'JAN' THEN Q.SecondLetter ELSE 0 END) "JAN2"

N.B: FirstLetter and SecondLetter are counted in the view.

Upvotes: 0

Views: 132

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

SELECT DISTINCT is almost never appropriate with GROUP BY.

Your problem is that you are including (MONTHNAMESHORT) in the GROUP BY.

Your query is very difficult to decipher. But it should look something like this:

SELECT SYEAR, DEPT_NAME,
       SUM(CASE WHEN upper(MONTHNAMESHORT) = 'JAN' THEN FirstLetter END) as "JAN1" ,  
       SUM(CASE WHEN upper(MONTHNAMESHORT) = 'JAN' THEN SecondLetter END) as "JAN2" ,  
       SUM(CASE WHEN upper(MONTHNAMESHORT) = 'FEB' THEN FirstLetter END) as "FEB1" ,  
       SUM(CASE WHEN upper(MONTHNAMESHORT) = 'FEB' THEN SecondLetter END) as "FEB2" 
FROM . . . 
GROUP BY SYEAR, DEPT_NAME;

Upvotes: 2

Related Questions