| View previous topic :: View next topic |
| Author |
Message |
lacoe1
Beginner
Joined: 24 Feb 2005
Posts: 33
Topics: 17
|
 Posted: Thu Oct 19, 2006 4:52 pm Post subject: Need Query to get the count based on year and orgin id |
 |
|
Example:
| Code: |
ORGID EMPLID CHECKDT
===== ====== ==========
10100 XXXXXX 10/13/2005
10100 XXXXXX 01/05/2006
10100 YYYYYY 09/02/2005
10100 YYYYYY 09/20/2005
10100 YYYYYY 10/05/2005
10100 YYYYYY 10/20/2006
10100 YYYYYY 11/04/2006
64000 ZZZZZZZ 09/02/2005
64000 ZZZZZZZ 09/20/2005
64000 ZZZZZZZ 10/05/2006
69000 MMMMMM 01/30/2006
89999 NNNNNNN 05/30/2005
|
For number of employees per Org who got paid in 2005 I need to display my sql output like this. Not interested to count for other years
-------------------------------------------------------------------------------------------
| Code: |
ORG EMPLCOUNT
10100 2
64000 1
69000 0
89999 1
|
Any suggestions ? |
|
| Back to top |
|
 |
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Thu Oct 19, 2006 5:24 pm Post subject: |
|
|
Are you showing the right results? I don't think no - Here's an untested SQL for actually what you had requested
| Code: |
SELECT ORGID
,COUNT(*)
FROM EMP
GROUP BY ORG
HAVING YEAR(CHECKDT) = '2005'
; |
________
Mega Cruiser
Last edited by coolman on Sat Feb 05, 2011 1:55 am; edited 1 time in total |
|
| Back to top |
|
|
lacoe1
Beginner
Joined: 24 Feb 2005
Posts: 33
Topics: 17
|
| Posted: Thu Oct 19, 2006 5:46 pm Post subject: |
|
|
coolman, your query will result in extra count, for example
10100 YYYYYY 09/02/2005
10100 YYYYYY 09/20/2005
10100 YYYYYY 10/05/2005
10100 YYYYYY 10/20/2006
10100 YYYYYY 11/04/2006
Even though YYYYY got paid 3 times in 2005 he should be counted as 1 employee.
Your query will result in 3. The correct count is 1. |
|
| Back to top |
|
|
lacoe1
Beginner
Joined: 24 Feb 2005
Posts: 33
Topics: 17
|
| Posted: Thu Oct 19, 2006 6:06 pm Post subject: |
|
|
I guess this will work me.A friend helped me
| Code: |
SELECT ORGID AS DISTRICT
,COUNT(DISTINCT EMPLID) AS EMPL_COUNT
FROM TABLE
WHERE CHECKDT BETWEEN '01/01/2005' AND '12/31/2005'
GROUP BY ORGID
WITH UR;
|
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
