Good afternoon
I need help on how to build a query from an Employee table:
Employee:(EmpID,
Name,
DateofBirth,
Sex,
Department,
Hiredate
Age)
1)I would like to build a query to count the number of females and males and their total in each departement.Then display the results using a table.
For example As departments:HR, PAYROLL, ACCOUNTING,IT and Customer Service
2) Is there a way in Windev to calculate the number of years in service based on the hiredate and the current date.

My French is not that good ...sorry
Your help will be really appreciated. I am new in Windev
Thanks

Hi !
To my mind and with my poor SQL knowledge, it seems to only be possible in two querries.
Sex is only male or female. The number of females is the total minus the males.

First querry wil count total for each department.
Second querry will then count males, differences will give females.

Querry 1 :
SELECT DEPARTMENT AS DEPARTMENT, COUNT(*) AS CNT FROM EMPLOYEE GROUP BY DEPARTMENT

Querry 2 :
SELECT DEPARTMENT AS DEPARTMENT, COUNT(*) AS MALES FROM EMPLOYEE WHERE SEX='male' GROUP BY DEPARTMENT

For the second point, you should find issues in SQL date functions supported by WinDev SQL (not allready in mind but will give it later).

Hi !

yau can try this for the first question

1)SELECT DEPARTMENT AS DEPARTMENT, SUM(CASE SEX WHEN 'MALE THEN 1 ELSE 0 END )) AS MALE ,SUM(CASE SEX WHEN 'FEMALE' THEN 1 ELSE 0 END)) AS FEMALE AS CNT FROM EMPLOYEE GROUP BY DEPARTMENT

or

1)SELECT DEPARTMENT AS DEPARTMENT, SUM(CASE WHEN SEX = 'MALE THEN 1 ELSE 0 END )) AS MALE ,SUM(CASE WHEN SEX='FEMALE' THEN 1 ELSE 0 END)) AS FEMALE AS CNT FROM EMPLOYEE GROUP BY DEPARTMENT
yuo have in the result for example :

Depat Male Female
HR 10 3
PAYROLL 5 7

@+

Thanks guys , I really appreciate your input . I will give it a try.
Have a nice day

Hi
I tried the query , it does not work .I always have an error on the WHEN which is in the query.
IF you could clarify me, I will really appreciated

Thanks