CODEWITHSUNDEEP
oracle-databasegroup-by
Thapipo
Thapipo

Reputation: 311

SQL Oracle (group by)

This is my output.

Name         Start      End
------      --------    --------
Mary        09-04-01    10-04-30
John        08-05-25    11-07-24
Alisa       07-07-07    11-06-28
Mary        06-06-06    12-12-20
John        05-05-05    12-12-20
Alex        04-04-04    11-12-20
Alisa       03-03-03    13-12-20
Mary        09-04-04    13-12-21
John        08-06-06    13-04-30
John        08-06-01    14-04-30

I need to GROUP BY every name, get the MIN() START for each person and get the MAX() END. Then, the difference between the START and the END like this : 

Name         Start      End         Difference
-------     --------    --------    ----------
Mary        06-06-06    13-12-21    MONTHS_BETWEEN(start,end)
John        05-05-05    14-04-30    MONTHS_BETWEEN(start,end)
Alisa       03-03-03    13-12-20    MONTHS_BETWEEN(start,end)
Alex        04-04-04    11-12-20    MONTHS_BETWEEN(start,end)

This is my query. I'm only able to get this result, I don't know why I can't GROUP BY everyname. It seems to be working correctly for the difference. I exclude the FROM.

SELECT table.name, MIN(tableDate.Start), MAX(tableDate.end), 
Round(MONTHS_BETWEEN(tableDate.end, tableDate.Start),0) as Difference
FROM (....)
GROUP BY table.name,tableDate.Start, tableDate.End
;

If I remove table.Date.Start and tableDate.End from the GROUP BY, Oracle is given me the error : 

ERROR at line 1:
ORA-00979: not a GROUP BY expression

tableDate.Start and tableDate.End are calculated columns so i dont know why i've to put them in the GROUP BY clause.

Thank you!

Upvotes: 0

Views: 121

Answers (2)

StuartLC
StuartLC

Reputation: 107387

All non-aggregates need to be in the Grouping, and Round(MONTHS_BETWEEN.... doesn't introduce tableDate.Start and tableDate.End with an aggregate. Depending on the interpretation of the data, it seems you want to use the already calculated MIN and MAX values in the MONTHS_BETWEEN calculation. How about this:

WITH cte AS
(
  SELECT table.name, MIN(tableDate.Start) AS MinStart, MAX(tableDate.end) as MaxEnd
  FROM (....)
  GROUP BY table.name
)
SELECT name, MinStart, MaxEnd, Round(MONTHS_BETWEEN(MaxEnd, MinStart),0) as Difference
FROM cte;

Upvotes: 0

Lamak
Lamak

Reputation: 70668

Well, you do need to group by the name column only. But one issue with your query is that the months between calculation needs to be using the MAX and MIN values. You are also using some reserved words as column names:

SELECT  T.name, 
        MIN(T.Start) AS Start, 
        MAX(T."end") AS "End",
        ROUND(MONTHS_BETWEEN(MAX(T."end"),MIN(T.Start)),0) as "Difference"
FROM YourTable T
GROUP BY T.name;

Upvotes: 1

Related Questions