question

bandder avatar image
bandder asked

How do I write the SQL statement to correctly join I need your help to get these statements in 1 row and multi-Columns

select to_char(SYSDATE, 'MONTH') as "CURRINT MONTH" from dual ; select count(PERSON_ID) as TOTAL_Nsaudi from company_person_all where ( company_person_all.employee_status = 'Active' ) and ( company_person_all.citizenship != 'SAUDI ARABIA' ) ; select count(PERSON_ID) as TOTAL_Saudi from company_person_all where ( company_person_all.employee_status = 'Active' ) and ( company_person_all.citizenship = 'SAUDI ARABIA' ) ; select count(PERSON_ID) as Total_All from company_person_all where ( ( company_person_all.citizenship != 'SAUDI ARABIA' or company_person_all.citizenship = 'SAUDI ARABIA' ) and company_person_all.employee_status = 'Active'
oraclepl-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
In SQL Server I would use a case statement, not sure exactly what you would use in Oracle, but maybe this might give you an idea..... select sum(case when company_person_all.citizenship 'SAUDI ARABIA' then 1 else 0 end ) as TOTAL_NSaudi, sum(case when company_person_all.citizenship = 'SAUDI ARABIA' then 1 else 0 end ) as TOTAL_Saudi, sum(1) as Total_All from company_person_all where company_person_all.employee_status = 'Active'
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This should work in Oracle 9 or better except the \<\> might be better off spelled as != and the word **and** is removed after **where**. In case if the Oracle version is 8 or older then **case** is not available, but **decode** is, so for older Oracle, this should do it:
select 
    sum(decode(company_person_all.citizenship, 
        'SAUDI ARABIA', 0, 1)) as TOTAL_NSaudi,
    sum(decode(company_person_all.citizenship, 
        'SAUDI ARABIA', 1, 0)) as TOTAL_Saudi,
    sum(1) as Total_All
    from company_person_all
    where
        company_person_all.employee_status = 'Active'
Oleg
1 Like 1 ·
thank Kev & oleg for your support mate, but i need one more thing, to track employment in current month by give prompt to enduser from "IFSAPP.EMP_EMPLOYED_TIME_ROW" say the only In employee at that month
0 Likes 0 ·
Can you please add more details in the question, like what query you already have. Thanks.
0 Likes 0 ·
here the query from IFSAPP.EMP_EMPLOYED_TIME_ROW 1 126 10 000368 5/1/2009 12/31/9999 Full Time - Open Contract 1 129 10 000369 5/1/2011 7/1/2012 Full Time - Open Contract 1 131 10 000370 5/1/2009 12/31/9999 Full Time - Open Contract 1 133 10 000371 5/23/2009 12/31/9999 Full Time - Open Contract i just need to add this statement to the one that Kav giving me so i can get track employment in current month by give prompt to enduser
0 Likes 0 ·
HERE THE STATEMENT THAT I NEED TO FIX SELECT to_char(SYSDATE, 'MONTH') AS "CURRENT MONTH", COUNT(CASE WHEN citizenship <> 'SAUDI ARABIA' THEN 1 END) AS TOTAL_NON_Saudi, COUNT(CASE WHEN citizenship = 'SAUDI ARABIA' THEN 1 END) AS TOTAL_Saudi, COUNT(*) AS Total_All FROM company_person_all WHERE (company_person_all.citizenship != 'SAUDI ARABIA' OR company_person_all.citizenship = 'SAUDI ARABIA') AND company_person_all.employee_status = 'Active'
0 Likes 0 ·
bandder avatar image
bandder answered

query.jpg (426.5 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The best would have been adding a sample data, output you need, tables fields and their relationships.
0 Likes 0 ·
SELECT to_char(SYSDATE, 'MONTH') AS "CURRENT MONTH", SUM(CASE WHEN a.citizenship <> 'SAUDI ARABIA' THEN 1 ELSE 0 END ) AS TOTAL_NON_Saudi, SUM(CASE WHEN a.citizenship = 'SAUDI ARABIA' THEN 1 ELSE 0 END ) AS TOTAL_Saudi, SUM(1) AS Total_All FROM company_person_all a JOIN IFSAPP.EMP_EMPLOYED_TIME_ROW T ON a.emp_no = t.emp_no AND SYSDATE between t.date_of_employment and t.date_of_leaving WHERE a.employee_status = 'Active' what i need rigt know is to get total form TOTAL_NON_Saudi+TOTAL_Saudi with same statemnt see attachment for more info![alt text][1] [1]: http://ask.sqlservercentral.com/storage/temp/125-124-query.jpg
0 Likes 0 ·
124-query.jpg (139.8 KiB)
Usman Butt avatar image
Usman Butt answered
What about this SELECT *,NVL(TOTAL_NON_Saudi,0) + NVL(TOTAL_Saudi,0) AS TOTAL_ALL FROM ( SELECT to_char(SYSDATE, 'MONTH') AS "CURRENT MONTH", SUM(CASE WHEN a.citizenship 'SAUDI ARABIA' THEN 1 ELSE 0 END ) AS TOTAL_NON_Saudi, SUM(CASE WHEN a.citizenship = 'SAUDI ARABIA' THEN 1 ELSE 0 END ) AS TOTAL_Saudi, FROM company_person_all a JOIN IFSAPP.EMP_EMPLOYED_TIME_ROW T ON a.emp_no = t.emp_no AND SYSDATE between t.date_of_employment and t.date_of_leaving WHERE a.employee_status = 'Active' ) FinalOutput
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bandder avatar image
bandder answered
it just give me "total all" what about the "month" & "non_saudi" & "saudi" i need it as what shown on the screen shot thank you again for your help
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.