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

Question

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'

Answer 1

0

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'

answered Feb 28 '12 at 08:39 AM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

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

Feb 28 '12 at 08:14 PM Oleg

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

Feb 29 '12 at 06:45 AM bandder

Can you please add more details in the question, like what query you already have. Thanks.

Feb 29 '12 at 07:05 AM Usman Butt

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

Feb 29 '12 at 07:20 AM bandder

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'

Feb 29 '12 at 07:47 AM bandder

(comments are locked)

Answer 2

0

alt text

query.jpg (436.8 kB)

answered Feb 29 '12 at 08:59 AM

bandder
1 ● 1 ● 1 ● 1

The best would have been adding a sample data, output you need, tables fields and their relationships.

Feb 29 '12 at 09:10 AM Sacred Jewel

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

124-query.jpg (143.2 kB)

Feb 29 '12 at 01:42 PM bandder

(comments are locked)

Answer 3

0

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

answered Feb 29 '12 at 02:07 PM

Usman Butt
13.8k ● 6 ● 8 ● 14

(comments are locked)

Answer 4

0

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

answered Feb 29 '12 at 03:28 PM

bandder
1 ● 1 ● 1 ● 1

(comments are locked)

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

Follow this question

Related Questions