CODEWITHSUNDEEP
sqloracle-databaseoracle11ggroup-by
vanBuren1738
vanBuren1738

Reputation: 117

Oracle 11g SQL Group by Quarter

Im trying to show the following information grouped by quarter:

Heres all ive come up with so far:

SELECT   CASE
             WHEN Start_Date >= '01-JAN-14' AND Start_Date <= '31-MAR-14' THEN 'Q1'
             WHEN Start_Date >= '01-APR-14' AND Start_Date <= '30-JUN-14' THEN 'Q2'
             WHEN Start_Date >= '01-JUL-14' AND Start_Date <= '30-SEP-14' THEN 'Q3'
             WHEN Start_Date >= '01-OCT-14' AND Start_Date <= '31-DEC-14' THEN 'Q4'
         END AS "Quarter",
         COUNT(Proj_Number) Num, SUM(COUNT(Emp_Num)),AVG(Hours_Used)
FROM     Project JOIN Assignment USING (Proj_Number) JOIN Employee USING (Emp_Num)
WHERE    Start_Date >= '01-JAN-14' AND Start_Date <= '31-DEC-14'
GROUP BY Start_Date,Emp_Num;

Upvotes: 1

Views: 4119

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think it is much easier to use to_char(xxx, 'Q') to get the quarter:

SELECT TO_CHAR(Start_Date, 'Q') AS "Quarter",
       COUNT(Proj_Number) as Num, COUNT(Emp_Num), AVG(Hours_Used)
FROM Project JOIN
     Assignment
     USING (Proj_Number) JOIN
     Employee
     USING (Emp_Num)
WHERE Start_Date >= DATE '2014-01-01' AND Start_Date <= DATE '2014-12-31'
GROUP BY TO_CHAR(Start_Date, 'Q');

Upvotes: 3

AvielNiego
AvielNiego

Reputation: 1233

You should be grouping by Quarter for the number of projects and empls working on those projects:

SELECT   CASE
         WHEN Start_Date >= '01-JAN-14' AND Start_Date <= '31-MAR-14' THEN 'Q1'
         WHEN Start_Date >= '01-APR-14' AND Start_Date <= '30-JUN-14' THEN 'Q2'
         WHEN Start_Date >= '01-JUL-14' AND Start_Date <= '30-SEP-14' THEN 'Q3'
         WHEN Start_Date >= '01-OCT-14' AND Start_Date <= '31-DEC-14' THEN 'Q4'
     END AS "Quarter",
     COUNT(Proj_Number) Num, COUNT(Emp_Num) emp_count
FROM     Project JOIN Assignment USING (Proj_Number) JOIN Employee USING (Emp_Num)
WHERE    Start_Date >= '01-JAN-14' AND Start_Date <= '31-DEC-14'
GROUP BY "Quarter";

and you should have a different query for the hours spent for each project

SELECT   CASE
             WHEN Start_Date >= '01-JAN-14' AND Start_Date <= '31-MAR-14' THEN 'Q1'
             WHEN Start_Date >= '01-APR-14' AND Start_Date <= '30-JUN-14' THEN 'Q2'
             WHEN Start_Date >= '01-JUL-14' AND Start_Date <= '30-SEP-14' THEN 'Q3'
             WHEN Start_Date >= '01-OCT-14' AND Start_Date <= '31-DEC-14' THEN 'Q4'
         END AS "Quarter",
         Proj_Number ,AVG(Hours_Used) hours_spent
FROM     Project JOIN Assignment USING (Proj_Number) JOIN Employee USING (Emp_Num)
WHERE    Start_Date >= '01-JAN-14' AND Start_Date <= '31-DEC-14'
GROUP BY "Quarter" ,Proj_Number;

You may want to join them to see all the information in one query

SELECT quarter.Quarter,quarter.Num as proj_count, quarter.emp_count,
                 proj.Proj_Number, proj.hours_spent
            FROM (SELECT   CASE
             WHEN Start_Date >= '01-JAN-14' AND Start_Date <= '31-MAR-14' THEN 'Q1'
             WHEN Start_Date >= '01-APR-14' AND Start_Date <= '30-JUN-14' THEN 'Q2'
             WHEN Start_Date >= '01-JUL-14' AND Start_Date <= '30-SEP-14' THEN 'Q3'
             WHEN Start_Date >= '01-OCT-14' AND Start_Date <= '31-DEC-14' THEN 'Q4'
         END AS "Quarter",
         COUNT(Proj_Number) Num, COUNT(Emp_Num) emp_count
    FROM     Project JOIN Assignment USING (Proj_Number) JOIN Employee USING (Emp_Num)
    WHERE    Start_Date >= '01-JAN-14' AND Start_Date <= '31-DEC-14'
    GROUP BY "Quarter") quarter, 
    (SELECT   CASE
                 WHEN Start_Date >= '01-JAN-14' AND Start_Date <= '31-MAR-14' THEN 'Q1'
                 WHEN Start_Date >= '01-APR-14' AND Start_Date <= '30-JUN-14' THEN 'Q2'
                 WHEN Start_Date >= '01-JUL-14' AND Start_Date <= '30-SEP-14' THEN 'Q3'
                 WHEN Start_Date >= '01-OCT-14' AND Start_Date <= '31-DEC-14' THEN 'Q4'
             END AS "Quarter",
             Proj_Number ,AVG(Hours_Used) hours_spent
    FROM     Project JOIN Assignment USING (Proj_Number) JOIN Employee USING (Emp_Num)
    WHERE    Start_Date >= '01-JAN-14' AND Start_Date <= '31-DEC-14'
    GROUP BY "Quarter" ,Proj_Number) proj
            WHERE quarter.Quarter = proj.Quarter

Upvotes: 1

Related Questions