CODEWITHSUNDEEP
sqlsql-server
Sayed Alesawy
Sayed Alesawy

Reputation: 445

Two count queries with different conditions on the same view

I have a database that contains 3 tables. 

Employee(SSN, Fname, Lname, Salary)
Project(Pnumber, Pname, Plocation)
Works_on(Essn, Pno, Hours)

I want to create a view that for each project shows its name (Pname), how many employees who work on that project getting paid more than 38k (More) and how many employees who work on that project getting paid less than 38k (Less).

My appraoch

    create view compare as 
    (
       (select count(E.SSN) as More, P.Pname
        from Employee as E, Project as P, Works_on as W
        where E.Salary > 38000 and W.Pno = P.Pnumber and E.SSN = W.Essn 
        group by(P.Pname)) union 

       (select count(E.SSN) as Less, P.Pname
        from Employee as E, Project as P, Works_on as W
        where E.Salary < 38000 and W.Pno = P.Pnumber and E.SSN = W.Essn 
        group by(P.Pname))
    );

But it shows only the (More) col and the project name.

Upvotes: 2

Views: 82

Answers (5)

tonypdmtr
tonypdmtr

Reputation: 3225

create view compare as
  select sum(case when Salary >= 38000 then 1 else 0 end) as More,
         sum(case when Salary <  38000 then 1 else 0 end) as Less,
         sum(case when Salary >= 38000 then 1 else -1 end) as Diff,
         Pname
    from Project join Works_on on Pno = Pnumber
    join Employee on SSN = Essn
    group by Pname;

Upvotes: 0

GermanC
GermanC

Reputation: 279

If you are sure about going for a view approach, this might work for you

select p.Pname
, sum(case when e.Salary < 38000 then 1 else 0 end) as under_38_k
, sum(case when e.Salary > 38000 then 1 else 0 end) as over_38_k
from Works_on w
join Employee e on e.ssn = w.essn
join Project p on p.Pnumber = w.Pno
group by p.Pname
order by p.Pname asc --customize as needed

Considerations:

  • Since we are using JOIN, which is short for INNER JOIN, projects with no assigned employees will not be displayed within the results. Switch to LEFT OUTER JOIN to Projects table if you'd like those to be showed as well with 0 as values.

  • Employees making exactly 38k are outside of the results as well. Feel free to include them by changing the case statement for one of the two columns.

If you want to make that limit customizable without redifining the view, you can define a function which takes the limit as a parameter like this:

CREATE FUNCTION EmployeesEarnings(@limit int)  
RETURNS TABLE  
AS  
RETURN  
select p.Pname
, sum(case when e.Salary < @limit then 1 else 0 end) as under_limit
, sum(case when e.Salary > @limit then 1 else 0 end) as over_limit
, sum(case when e.Salary < @limit then 1 else 0 end) - sum(case when e.Salary > @limit then 1 else 0 end) as difference
from Works_on w
join Employee e on e.ssn = w.essn
join Project p on p.Pnumber = w.Pno
group by p.Pname

and then call the function as if it was a table:

select *
from EmployeesEarnings(38000)

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

Use conditional SUM in place of COUNT:

select
    SUM(CASE WHEN E.Salary >= 38000 THEN 1 ELSE 0 END) as More
,   SUM(CASE WHEN E.Salary < 38000 THEN 1 ELSE 0 END) as Less
,   P.Pname
from Employee as E
join Works_on as W ON E.SSN = W.Essn 
join Project as P ON W.Pno = P.Pnumber
group by(P.Pname)

Note the use of ANSI joins (you should switch to ANSI syntax) and use of >= in place of > (otherwise Salary of exactly 38000 would not get counted).

Upvotes: 2

Thom A
Thom A

Reputation: 95561

You've misunderstanding how UNION works. UNION combines the dataset, it doesn't add exact columns to it; and it uses the alias's supplied in the first query. Thus your column, Less, will have it's values in the output of column More. If you want the columns columns to be displayed, try adding a column with a value of NULL to both datasets. Also, get rid of those awful implicite JOINs. Use JOIN and ON:

CREATE VIEW Compare AS
      SELECT COUNT(E.SSN) as More,
             NULL AS Less,
             P.Pname
      FROM Employee E
           JOIN Project P ON W.Pno = P.Pnumber
           JOIN Works_on W ON E.SSN = W.Essn 
      WHERE E.Salary > 38000 
      GROUP BY P.Pname
      UNION 
      SELECT NULL as More,
             COUNT(E.SSN) AS Less,
             P.Pname
      FROM Employee E
           JOIN Project P ON W.Pno = P.Pnumber
           JOIN Works_on W ON E.SSN = W.Essn 
      WHERE E.Salary < 38000 
      GROUP BY P.Pname;

This, however, can be simplified to

CREATE VIEW Compare AS

      SELECT CASE WHEN E.Salary > 38000 THEN COUNT(E.SSN) END AS More,
             CASE WHEN E.Salary < 38000 THEN COUNT(E.SSN) END AS Less,
             P.Pname
      FROM Employee E
           JOIN Project P ON W.Pno = P.Pnumber
           JOIN Works_on W ON E.SSN = W.Essn 
      GROUP BY P.Pname;

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

First, a view might not be appropriate for this situation because the number 38000 might change over time.

Second, a case construct seems appropriate for this situation. The general idea is:

select case when salary >= 38000 then 'more' else 'less' end salaryRange
, count(*) employees
etc
group by case when salary >= 38000 then 'more' else 'less' end

Upvotes: 0

Related Questions