You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Query expression with COUNT

1 reply [Last post]
Guest's picture
Guest

hi,
i am developing a query to show data with STATUS field , this field have two values 'I' and 'P'.
Now to select data for all EMPLIDs with status field value 'I' when count is more than two rows and with status field value 'P' when count more than one row.

what is the syntax for this in where clause.

BM's picture
BM (not verified)
Re: Query expression with COUNT

Try something like this:

SELECT EMPLID,ACTION,COUNT(*) FROM PS_JOB WHERE ACTION = 'HIR' GROUP BY EMPLID,ACTION HAVING COUNT(*) = 2
UNION
SELECT EMPLID,ACTION,COUNT(*) FROM PS_JOB WHERE ACTION = 'TER' GROUP BY EMPLID,ACTION HAVING COUNT(*) = 1
ORDER BY 1