query

Hello,
I wondered if anyone can help me? i'm trying to query a table, although
want to preform two searches on the same table/column then display the
results.. these are my two select statements. how can I join them
together so they display side by side.
SELECT COUNT(staff.sex) as "Number of Female members"
FROM staff
WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
SELECT COUNT(staff.sex) as "Number of Male members"
FROM staff
WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
Thanks for you helpThe following example should return the desired results:
CREATE TABLE dbo.Staff
(
sex VARCHAR(10),
deptName VARCHAR(20)
)
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'Other department'
INSERT dbo.Staff SELECT 'Male', 'Other department'
SELECT COUNT(SEX) AS [Number of Male members],
(SELECT COUNT(SEX)
FROM dbo.staff
WHERE sex = 'Female'
AND deptName = 'CIS department') AS [Number of Female members]
FROM dbo.staff
WHERE sex = 'Male'
AND deptName = 'CIS department'
HTH
- Peter Ward
WARDY IT Solutions
"davidjohnlong@.googlemail.com" wrote:
> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>|||If you would rather return your data in rows, you could try:
SELECT COUNT(sex) [Number of members],
CASE sex
WHEN 'Mmale' THEN 'Male'
WHEN 'Female' THEN 'Female'
ELSE 'Unknown'
END [Sex]
FROM staff
WHERE deptName = 'CIS department'
GROUP BY [sex]|||davidjohnlong@.googlemail.com a écrit :
> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>
SELECT SUM(CASE staff.sex
WHEN 'Female' THEN 1
WHEN 'Male' THEN 0
END) AS "Number of Female members",
SUM(CASE staff.sex
WHEN 'Female' THEN 0
WHEN 'Male' THEN 1
END) AS "Number of Male members"
FROM staff
WHERE staff.deptName = 'CIS department'
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************sql