June 14, 2018 at 10:37 pm
Hi Friends,
I'm a newbie. But I was wondering if i had an employee table joined with a department table with a column named gender in the employee table ; how would i get a count of employees that are female and a count of employees that are male in each department? Thank You !
June 15, 2018 at 12:42 am
This is a quite a simple query in terms of SQL. What have you tried so far? You'll learn more by researching rather than someone simply giving you the answer.
You need look for JOIN, GROUP BY, COUNT, and depending how you want your data, possibly CASE.
Have a go, and if you get stuck reply with what you've tried.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 15, 2018 at 9:56 am
As Thom suggested, make an attempt.
Use the gender to group data.
June 15, 2018 at 1:33 pm
I have spent a lot of time trying. I tried using group by, but I think it's a little more complex than that. But Thanks for your help
June 15, 2018 at 1:56 pm
From the employees table joined to the department table, you'll want to select the department and employee gender and a count. You'll need to group by the department and gender.
It'll be something along the lines of this query, but the odds of this being the exact query are very low because we don't know your table and column names.
SELECT d.Name, e.Gender, COUNT(*)
FROM dbo.Departments d
INNER JOIN dbo.Employees e ON e.DepartmentID = d.ID
GROUP BY d.Name, e.Gender
ORDER BY d.Name, e.Gender;
There are other ways of doing this, depending on the exact results you want to see, but the principle is similar.
For a tested query, we'll need the table definitions. I hope this helps get you closer.
June 15, 2018 at 2:37 pm
Ronnie65 - Friday, June 15, 2018 1:33 PMI have spent a lot of time trying. I tried using group by, but I think it's a little more complex than that. But Thanks for your help
Your haven't posted what you've tried though, like o suggest. Could you do so please? Then we can help you further, as we can see where you went wrong.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 15, 2018 at 3:27 pm
Why did you fail to post DDL? I guess we'll just have to do it for you. A table represents a set of things, so unless you really do have only one employee, one of your tables should have been named with a collective or plural name. Etc, Here's my guess at what you meant.
CREATE TABLE Personnel
(emp_id CHAR(10) NOT NULL PRIMARY KEY,
sex_code CHAR(1) NOT NULL
CHECK (sex_code IN ('0', '1', '2', '9')), ---iso standard
..);
CREATE TABLE Departments
(dept_id CHAR(5) NOT NULL PRIMARY KEY,
dept_name VARCHAR(20) NOT NULL,
..);
CREATE TABLE Job_Assignments
(emp_id CHAR(10) NOT NULL
REFERENCES Personnel,
dept_id CHAR(5) NOT NULL
REFERENCES Departments (dept_id),
PRIMARY KEY (emp_id, dept_id),
..);
WITH X
AS
(SELECT D.dept_name, P.emp_id
FROM Departments AS D,
Personnel AS P,
Job_Assignments AS J
WHERE J.dept_id = D.dept_id
AND J.emp_id = P.emp_id)
SELECT dept_name,
SUM(CASE WHEN sex_code = '1' THEN 1 ELSE 0 END) AS male_cnt,
SUM(CASE WHEN sex_code = '2' THEN 1 ELSE 0 END) AS female_cnt
FROM X
GROUP BY dept_name;
Now when you submit this for your homework, you're going to have to know enough SQL to explain it to your teacher 🙂 and is probably will to ask you about normalization, references, ISO standards, etc.
Please post DDL and follow ANSI/ISO standards when asking for help.
June 16, 2018 at 1:29 pm
Thanks Everyone for your help. You showed me 2 different ways of doing this. All I need to do is group by Gender and use the COUNT function. This is not homework I'm just trying to learn TSQL. Thanks !
June 18, 2018 at 5:34 am
Ronnie65 - Saturday, June 16, 2018 1:29 PMThanks Everyone for your help. You showed me 2 different ways of doing this. All I need to do is group by Gender and use the COUNT function. This is not homework I'm just trying to learn TSQL. Thanks !
Glad to hear we could help; thanks for the feedback. This was a pretty simple question, but not having the DDL for the tables forced us to guess. Not all problems are going to be able to be solved by guesses. Remember, we can't see what you see on your system.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply