SQL Count function

Question

I'm a student this is part of a homework assignment. Thanks for any advice.

I have 2 tables.
Employee has a column last_name, Job_title has a exempt_non_exempt column it's data type is bit. 0 for hourly 1 for salary The primary key and foreign key is job_title for both tables.

I need to find out How many employees are salaried and how many are hourly? I can only use one statement. So I need to COUNT and JOIN

I have 10 employees and 8 are hourly and 2 are salaried.

*

This code shows the count as 7

Select Employee.Last_name, Job_title.Exempt_Non_Exempt_Status,

COUNT  (Exempt_Non_Exempt_Status)

from Employee, Job_title

where Exempt_Non_Exempt_Status=0

group by Employee.Last_name, Job_title.Exempt_Non_Exempt_Status

I started with this and can change the status to =o or =1 but that requires 2 Select statements

SELECT LAST_NAME  FROM Employee

JOIN JOB_TITLE

ON EMPLOYEE.JOB_TITLE=JOB_TITLE.JOB_TITLE

WHERE Exempt_Non_Exempt_Status

=0 

ORDER BY Last_name

Answer 1

Consider exactly what data you need to retrieve - this should point you in the correct direction for the columns you need in the select clause and your joins.

Answer 2

On SQL server, you can use a case statement with the count, for example:

SELECT COUNT(CASE WHEN SKY = 'blue' THEN 1 ELSE 0 END) 'blueskies'
SELECT COUNT(CASE WHEN SKY = 'red' THEN 1 ELSE 0 END) 'redskies'
  FROM planets

That would count the number of planets with skies that are blue, and the number of planets with skies that are red, in one statement (and one row).

Alternatively, you could UNION the two queries you have written to return a joint resultset. This is technically one statement, but it might not do for your homework.

Answer 3

Your where clause isn't needed and actually breaks the query here. And you haven't added a join clause.

I suppose that this query would suffice:

SELECT
    [the columns you want to select]
FROM Employee
JOIN Job_title ON Employee.Job_title = Job_title.Job_title
GROUP BY
    Employee.Last_name,
    Job_title.Exempt_Non_Exempt_Status

Answer 4

Try something like this:

select  Last_Name,
sum(exempt) as [Hourly],
sum(exempt) as [Salary],
from employees e inner join Job Title j on e.last_name = j.last_name
Group by last_name, exempt

Answer 5

Try:

SELECT J.Exempt_Non_Exempt_Status, count(*)
FROM Employee E
JOIN Job_title J ON E.Job_title = J.Job_title
GROUP BY J.Exempt_Non_Exempt_Status

This should return two rows - one with a count of salaried employees, one with a count of hourly employees.