Styjun

Acoustic guitar chords' positions vs those of a Bass guitar

Why is DC so, so, so Democratic?

Did Don Young threaten John Boehner with a 10 inch blade to the throat?

How do you structure large embedded projects?

Finding Greatest Common Divisor using LuaLatex

Host telling me to cancel my booking in exchange for a discount?

Hats Question: Confusion Over Its Formulation

What would be the effects of (relatively) widespread precognition on the stock market?

1025th term of the given sequence.

Cargo capacity of a kayak

My guitar strings go loose when I tighten them?

Is it OK to accept a job opportunity while planning on not taking it?

Why did modems have speakers?

What is the best word describing the nature of expiring in a short amount of time, connoting "losing public attention"?

Is it better to merge "often" or only after completion do a big merge of feature branches?

Create Circle with Inner Radius

As the Ferris wheel turns

Quickest way to move a line in a text file before another line in a text file?

Has Iron Man made any suit for underwater combat?

Pass USB 3.0 connection through D-SUB connector

Found old paper shares of Motorola Inc that has since been broken up

How can I deal with someone that wants to kill something that isn't supposed to be killed?

Why does the salt in the oceans not sink to the bottom?

Storyboarding Approaches for the Non-Artistic

Summing up GROUP BY results

MySQL Query GROUP BY day / month / yearInsert results of a stored procedure into a temporary tableGroup By Multiple ColumnsRetrieving the last record in each group - MySQLUsing group by on multiple columnsSelect first row in each GROUP BY group?SQL Group By function(column) - Now can't Select that columnGroup by in LINQReason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clauseHow does Subquery in select statement work in oracle

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I'm learning SQL using this Oracle database.

I ran into some problems with the task: display the number of all employees and the number of people hired in 2004, 2005, 2006 year after year.

I can do these two parts separately but the teacher said it has to be handled within one query and no subqueries are needed.

SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES;

The above line counts all employees.

SELECT EXTRACT(YEAR FROM HIRE_DATE) AS YEAR, COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES 
FROM EMPLOYEES 
WHERE EXTRACT(YEAR FROM HIRE_DATE) IN (2004, 2005, 2006) 
GROUP BY EXTRACT(YEAR FROM HIRE_DATE);

And the above part works fine for counting people hired in 2004, 2005, 2006.

I think maybe there's a way to sum my results grouped by year and get the overall number of employees within this single query but the WHERE part is the obstacle. I'd appreciate any suggetions, probably as simple as possible as it was only my second class of writing queries.

sql oracle group-by

share|improve this question

edited Mar 26 at 13:31

a_horse_with_no_name

323k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 13:30

ThetaTheta

11077 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  UNION both queries? Sneaky trick...
  
  – The Impaler
  Mar 26 at 13:36
  

  
  
  
  

add a comment | 

0

I'm learning SQL using this Oracle database.

I ran into some problems with the task: display the number of all employees and the number of people hired in 2004, 2005, 2006 year after year.

I can do these two parts separately but the teacher said it has to be handled within one query and no subqueries are needed.

SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES;

The above line counts all employees.

SELECT EXTRACT(YEAR FROM HIRE_DATE) AS YEAR, COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES 
FROM EMPLOYEES 
WHERE EXTRACT(YEAR FROM HIRE_DATE) IN (2004, 2005, 2006) 
GROUP BY EXTRACT(YEAR FROM HIRE_DATE);

And the above part works fine for counting people hired in 2004, 2005, 2006.

I think maybe there's a way to sum my results grouped by year and get the overall number of employees within this single query but the WHERE part is the obstacle. I'd appreciate any suggetions, probably as simple as possible as it was only my second class of writing queries.

sql oracle group-by

share|improve this question

edited Mar 26 at 13:31

a_horse_with_no_name

323k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 13:30

ThetaTheta

11077 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  UNION both queries? Sneaky trick...
  
  – The Impaler
  Mar 26 at 13:36
  

  
  
  
  

add a comment | 

I'm learning SQL using this Oracle database.

I ran into some problems with the task: display the number of all employees and the number of people hired in 2004, 2005, 2006 year after year.

I can do these two parts separately but the teacher said it has to be handled within one query and no subqueries are needed.

SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES;

The above line counts all employees.

SELECT EXTRACT(YEAR FROM HIRE_DATE) AS YEAR, COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES 
FROM EMPLOYEES 
WHERE EXTRACT(YEAR FROM HIRE_DATE) IN (2004, 2005, 2006) 
GROUP BY EXTRACT(YEAR FROM HIRE_DATE);

And the above part works fine for counting people hired in 2004, 2005, 2006.

I think maybe there's a way to sum my results grouped by year and get the overall number of employees within this single query but the WHERE part is the obstacle. I'd appreciate any suggetions, probably as simple as possible as it was only my second class of writing queries.

sql oracle group-by

share|improve this question

edited Mar 26 at 13:31

a_horse_with_no_name

323k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 13:30

ThetaTheta

11077 bronze badges

SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES;

SELECT EXTRACT(YEAR FROM HIRE_DATE) AS YEAR, COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES 
FROM EMPLOYEES 
WHERE EXTRACT(YEAR FROM HIRE_DATE) IN (2004, 2005, 2006) 
GROUP BY EXTRACT(YEAR FROM HIRE_DATE);

share|improve this question

edited Mar 26 at 13:31

a_horse_with_no_name

323k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 13:30

ThetaTheta

11077 bronze badges

share|improve this question

edited Mar 26 at 13:31

a_horse_with_no_name

323k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 13:30

ThetaTheta

11077 bronze badges

edited Mar 26 at 13:31

a_horse_with_no_name

323k5151 gold badges503503 silver badges602602 bronze badges

edited Mar 26 at 13:31

a_horse_with_no_name

323k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 13:30

ThetaTheta

11077 bronze badges

asked Mar 26 at 13:30

ThetaTheta

11077 bronze badges

2 Answers
2

active

oldest

votes

2

This gives you the yearly total -Note this is total employees ever - If you only want Count for the 3 years then add your WHERE Clause back in.

SELECT COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES ,
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2016 THEN 1 ELSE 0 END) as [2016 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2017 THEN 1 ELSE 0 END) as [2017 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2018 THEN 1 ELSE 0 END ) as [2018 Hire Count]
FROM EMPLOYEES ;

share|improve this answer

answered Mar 26 at 13:38

john McTighejohn McTighe

1,07344 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Exactly what I came to. +1
  
  – The Impaler
  Mar 26 at 13:41
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just a note. There's no need for ELSE since in SQL the default value is NULL and SUM() ignores it.
  
  – The Impaler
  Mar 26 at 13:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So this actually counts the number of rows with year values 2016, 2017, 2018 separately, could you explain what the "1 else 0" part does?
  
  – Theta
  Mar 26 at 13:47
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Hi Theta - This is a quick way of counting the rows that match the criteria. EG - If you imagine each row will have a hire date and if that is the right year then a 1 is set otherwise a 0- than all these 1s and 0s are added together.
  
  – john McTighe
  Mar 26 at 13:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @johnMcTighe A really nice explanation and a trick I'll surely use, thanks a lot!
  
  – Theta
  Mar 26 at 14:00
  

  
  
  
  

add a comment | 

1

I would do something like:

with
x as (
 select extract(year from hire_date) as year from employees
)
select
 count(*) as total,
 sum(case when year = 2004 then 1 end) as hired_2004,
 sum(case when year = 2005 then 1 end) as hired_2005,
 sum(case when year = 2006 then 1 end) as hired_2006
from x

share|improve this answer

answered Mar 26 at 13:40

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55358416%2fsumming-up-group-by-results%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

This gives you the yearly total -Note this is total employees ever - If you only want Count for the 3 years then add your WHERE Clause back in.

SELECT COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES ,
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2016 THEN 1 ELSE 0 END) as [2016 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2017 THEN 1 ELSE 0 END) as [2017 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2018 THEN 1 ELSE 0 END ) as [2018 Hire Count]
FROM EMPLOYEES ;

share|improve this answer

answered Mar 26 at 13:38

john McTighejohn McTighe

1,07344 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Exactly what I came to. +1
  
  – The Impaler
  Mar 26 at 13:41
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just a note. There's no need for ELSE since in SQL the default value is NULL and SUM() ignores it.
  
  – The Impaler
  Mar 26 at 13:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So this actually counts the number of rows with year values 2016, 2017, 2018 separately, could you explain what the "1 else 0" part does?
  
  – Theta
  Mar 26 at 13:47
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Hi Theta - This is a quick way of counting the rows that match the criteria. EG - If you imagine each row will have a hire date and if that is the right year then a 1 is set otherwise a 0- than all these 1s and 0s are added together.
  
  – john McTighe
  Mar 26 at 13:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @johnMcTighe A really nice explanation and a trick I'll surely use, thanks a lot!
  
  – Theta
  Mar 26 at 14:00
  

  
  
  
  

add a comment | 

2

This gives you the yearly total -Note this is total employees ever - If you only want Count for the 3 years then add your WHERE Clause back in.

SELECT COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES ,
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2016 THEN 1 ELSE 0 END) as [2016 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2017 THEN 1 ELSE 0 END) as [2017 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2018 THEN 1 ELSE 0 END ) as [2018 Hire Count]
FROM EMPLOYEES ;

share|improve this answer

answered Mar 26 at 13:38

john McTighejohn McTighe

1,07344 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Exactly what I came to. +1
  
  – The Impaler
  Mar 26 at 13:41
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just a note. There's no need for ELSE since in SQL the default value is NULL and SUM() ignores it.
  
  – The Impaler
  Mar 26 at 13:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So this actually counts the number of rows with year values 2016, 2017, 2018 separately, could you explain what the "1 else 0" part does?
  
  – Theta
  Mar 26 at 13:47
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Hi Theta - This is a quick way of counting the rows that match the criteria. EG - If you imagine each row will have a hire date and if that is the right year then a 1 is set otherwise a 0- than all these 1s and 0s are added together.
  
  – john McTighe
  Mar 26 at 13:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @johnMcTighe A really nice explanation and a trick I'll surely use, thanks a lot!
  
  – Theta
  Mar 26 at 14:00
  

  
  
  
  

add a comment | 

This gives you the yearly total -Note this is total employees ever - If you only want Count for the 3 years then add your WHERE Clause back in.

SELECT COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES ,
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2016 THEN 1 ELSE 0 END) as [2016 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2017 THEN 1 ELSE 0 END) as [2017 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2018 THEN 1 ELSE 0 END ) as [2018 Hire Count]
FROM EMPLOYEES ;

share|improve this answer

answered Mar 26 at 13:38

john McTighejohn McTighe

1,07344 silver badges88 bronze badges

SELECT COUNT(EMPLOYEE_ID) AS HIRED_EMPLOYEES ,
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2016 THEN 1 ELSE 0 END) as [2016 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2017 THEN 1 ELSE 0 END) as [2017 Hire Count],
SUM(CASE WHEN EXTRACT(YEAR FROM HIRE_DATE) AS YEAR = 2018 THEN 1 ELSE 0 END ) as [2018 Hire Count]
FROM EMPLOYEES ;

share|improve this answer

answered Mar 26 at 13:38

john McTighejohn McTighe

1,07344 silver badges88 bronze badges

answered Mar 26 at 13:38

john McTighejohn McTighe

1,07344 silver badges88 bronze badges

answered Mar 26 at 13:38

john McTighejohn McTighe

1,07344 silver badges88 bronze badges

1

I would do something like:

with
x as (
 select extract(year from hire_date) as year from employees
)
select
 count(*) as total,
 sum(case when year = 2004 then 1 end) as hired_2004,
 sum(case when year = 2005 then 1 end) as hired_2005,
 sum(case when year = 2006 then 1 end) as hired_2006
from x

share|improve this answer

answered Mar 26 at 13:40

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

add a comment | 

1

I would do something like:

with
x as (
 select extract(year from hire_date) as year from employees
)
select
 count(*) as total,
 sum(case when year = 2004 then 1 end) as hired_2004,
 sum(case when year = 2005 then 1 end) as hired_2005,
 sum(case when year = 2006 then 1 end) as hired_2006
from x

share|improve this answer

answered Mar 26 at 13:40

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

add a comment | 

I would do something like:

with
x as (
 select extract(year from hire_date) as year from employees
)
select
 count(*) as total,
 sum(case when year = 2004 then 1 end) as hired_2004,
 sum(case when year = 2005 then 1 end) as hired_2005,
 sum(case when year = 2006 then 1 end) as hired_2006
from x

share|improve this answer

answered Mar 26 at 13:40

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

with
x as (
 select extract(year from hire_date) as year from employees
)
select
 count(*) as total,
 sum(case when year = 2004 then 1 end) as hired_2004,
 sum(case when year = 2005 then 1 end) as hired_2005,
 sum(case when year = 2006 then 1 end) as hired_2006
from x

share|improve this answer

answered Mar 26 at 13:40

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

answered Mar 26 at 13:40

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

answered Mar 26 at 13:40

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges