Subject | RE: [firebird-support] This SQL conundrum is breaking my head |
---|---|
Author | Clay Shannon |
Post date | 2005-04-26T14:13:30Z |
<<
1.
If you've no problems with a row per action (what i prefer):
SELECT
a.gender,
a.ActionTaken,
Count(*)
FROM
Applicants a
GROUP BY
a.gender,
a.ActionTaken
2.
in FB1.5 if you want the results in columns:
SELECT
a.gender,
SUM(CASE a.ActionTaken = 'Hired' THEN 1 ELSE 0 END) AS Hired,
SUM(CASE a.ActionTaken = 'Not Interviewed' THEN 1 ELSE 0 END) AS "Not
Interviewed",
SUM(CASE a.ActionTaken = 'Interviewed-job declined' THEN 1 ELSE 0 END) AS
"Interviewed-job
declined",
SUM(CASE a.ActionTaken = 'Did not meet qualifications' THEN 1 ELSE 0 END)
AS "Did not meet
qualifications"
FROM
Applicants a
GROUP BY
a.gender
ActionTaken to ActionCode); the second (2.), though (slightly modified to
reflect the actual name of the column (ActionCode) and its contents)...
SELECT
a.gender,
SUM(CASE a.ActionCode = 1 THEN 1 ELSE 0 END) AS Hired,
SUM(CASE a.ActionCode = 2 THEN 1 ELSE 0 END) AS NotInterviewed,
SUM(CASE a.ActionCode = 3 THEN 1 ELSE 0 END) AS Interviewedjobdeclined,
SUM(CASE a.ActionCode = 4 THEN 1 ELSE 0 END) AS Didnotmeetqualifications
FROM
HR_Applicants a
GROUP BY
a.gender
...gives the err msg:
Error code -104, token unknown, =
(it chokes on the first equals sign).
1.
If you've no problems with a row per action (what i prefer):
SELECT
a.gender,
a.ActionTaken,
Count(*)
FROM
Applicants a
GROUP BY
a.gender,
a.ActionTaken
2.
in FB1.5 if you want the results in columns:
SELECT
a.gender,
SUM(CASE a.ActionTaken = 'Hired' THEN 1 ELSE 0 END) AS Hired,
SUM(CASE a.ActionTaken = 'Not Interviewed' THEN 1 ELSE 0 END) AS "Not
Interviewed",
SUM(CASE a.ActionTaken = 'Interviewed-job declined' THEN 1 ELSE 0 END) AS
"Interviewed-job
declined",
SUM(CASE a.ActionTaken = 'Did not meet qualifications' THEN 1 ELSE 0 END)
AS "Did not meet
qualifications"
FROM
Applicants a
GROUP BY
a.gender
>>I ran the SQL above in DBWB. The first (1.) worked fine (changing
ActionTaken to ActionCode); the second (2.), though (slightly modified to
reflect the actual name of the column (ActionCode) and its contents)...
SELECT
a.gender,
SUM(CASE a.ActionCode = 1 THEN 1 ELSE 0 END) AS Hired,
SUM(CASE a.ActionCode = 2 THEN 1 ELSE 0 END) AS NotInterviewed,
SUM(CASE a.ActionCode = 3 THEN 1 ELSE 0 END) AS Interviewedjobdeclined,
SUM(CASE a.ActionCode = 4 THEN 1 ELSE 0 END) AS Didnotmeetqualifications
FROM
HR_Applicants a
GROUP BY
a.gender
...gives the err msg:
Error code -104, token unknown, =
(it chokes on the first equals sign).