VBA - SQL - Include records with zero count

Question

I'm new to SQL fiddling with JOINS & failing to get the outcome. Any help would be truly appreciated.

Requirement

I've got a table, DATA1 & a copy of it called DATA2. The columns in it are EMPID,JOINING_DATE,DESIGNATION,COMMENTS,GENDER,DEPARTMENT

I've got VB code which selects data from the excel sheet containing the above 2 sheets using ADO. When I run a query say, get me the count of number of employees who joined IT department every month between 01/2011 & 03/2011, I expect to see result similar to the below

01/2011 0
02/2011 1
03/2011 0

Sample Data

I've created sample data below

1|01/2011|AAA|DG1|asdasdasd|Male|Sales
2|01/2011|BBB|DG1|asdasdasd|Female|Networking
3|02/2011|AAA|DG7|asdasdasd|Male|Sales
4|02/2011|AAA|DG5|asdasdasd|Female|IT
5|03/2011|AAA|DG1|asdasdasd|Male|MArketing

Please can someone help me here?

Gordon Linoff · Accepted Answer · 2014-02-28 02:55:32Z

1

Assuming you have someone who started in each month:

select year(joining_date), month(joining_date),
       sum(case when department = 'IT' then 1 else 0 end)
from sample 
group by year(joining_date), month(joining_date)
order by 1, 2;

answered Feb 28, 2014 at 2:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

User12345 Over a year ago

Hello Gordon,Thanks for this. With ADO, "when" doesn't work. I've looked around for workarounds but can't get anything that works. Is there an alternate for this please?

Gordon Linoff Over a year ago

What database are you using?

Collectives™ on Stack Overflow

VBA - SQL - Include records with zero count

Requirement

Sample Data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Requirement

Sample Data

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related