SQL Server Performance Forum – Threads Archive

SQL Query Help

Table Employee has employee_id, employee_type. Following are example values in employee table. employee_id employee_type
1 manager
2 clerk
3 manager
4 clerk
5 accountant
Requirement: Retrieve count of employees from employee table where employee_type = ‘manager’ or employee_type = ‘supervisor’. If rows are not found for an employee_type, then return the count as zero. NOTE: ‘supervisor’ is not in the list above. The query I could come up with:
select employee_type, count(employee_type)
from employee
where employee_type = ‘manager’
UNION
select employee_type, count(employee_type)
from employee
where employee_type = ‘supervisor’ This query returns: employee_type count(employee_type)
manager 2 But it doesn’t return the row for ‘supervisor’. How do I modify the query to return the result as: employee_type count(employee_type)
manager 2
supervisor 0
Thankyou.

Assuming you have an EmployeeType table, where you do have a row for "supervisor" … SELECT ET.EmployeeType, COUNT(E.employeeid)
FROM EmployeeType ET
LEFT JOIN Employee E ON ET.EmployeeType = ET.EmployeeType
WHERE ET.EmployeeType IN (‘manager’, ‘supervisor’)
GROUP BY ET.EmployeeType You use the LEFT JOIN to cover all employee types, not filtering for ones that are actually used in the Employee table. Now the trick is to count the values from the outer table: if there’s no match, then the count is 0. If you would use COUNT(*), then the count would be 1 for the row from the inner table.

Something like this select ‘manager’, count(*)
from employee
where employee_type = ‘manager’
UNION ALL
select ‘supervisor’, count(*)
from employee
where employee_type = ‘supervisor’ should return both rows for you.

Thanks so much for your replies. I appreciate your help. Can you please help me with the following as well? Claim_Type table has the following: Id Description 1 Dental claims
2 Medical claims Employee table has the following: Id Name
1 John
2 Tom
3 Isaac Employee_claim table has the following: EmployeeId ClaimId Date
1 1 04/04/2005
1 2 05/04/2005
2 1 03/04/2005
2 2 04/30/2005
1 2 06/30/2005 Now I need to retrieve rows as follows: Employee Dentalclaims(count) Medicalclaims(count)
John 1 2
Tom 1 1 Query that I wrote: Select employee_name, claim_id, count(claimId)
from employee, employee_claim
where employee.employee_id = employee_claim.employee_id
group by employee_claim.claimId This returns the result as: Employee cliam_id count(claim_id)
John 1 1
John 2 2
Tom 1 1 Can someone help to retrieve the result in one row. Like employeeName, count(medical claims), count(dental claims)? Thank you.

Just out of curiosity. Are these homeworks assigned to you? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Just out of curiosity. Are these homeworks assigned to you? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Luis Martin Moderator SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason. Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Can anyone provide a sql query to solve this issue… Thanks.

Okay, I’ll help a bit… [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Check out "cross-tab reports" in the SQL Server Books Online. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Thanks, Frank. That is what I was looking for. URL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp I came across this forum few days back. This forum is really helpful, for developers. Keep up the good work.

yes… actually we already have an index on the Account_number as a bigint.
but the customer wants to search to work as if the number is a character (which works a bit differently).

quote:Originally posted by taaSarge yes… actually we already have an index on the Account_number as a bigint.
but the customer wants to search to work as if the number is a character (which works a bit differently).

Is this related to this thread? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

I think you need to reply to this topic
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14702 Madhivanan Failing to plan is Planning to fail

]]>