x

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'
more ▼

asked Feb 28, 2012 at 08:12 AM in Default

bandder gravatar image

bandder
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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'
more ▼

answered Feb 28, 2012 at 08:39 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

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, 2012 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, 2012 at 06:45 AM bandder
Can you please add more details in the question, like what query you already have. Thanks.
Feb 29, 2012 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, 2012 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, 2012 at 07:47 AM bandder
(comments are locked)
10|1200 characters needed characters left
query.jpg (436.8 kB)
more ▼

answered Feb 29, 2012 at 08:59 AM

bandder gravatar image

bandder
1 1 1 1

The best would have been adding a sample data, output you need, tables fields and their relationships.
Feb 29, 2012 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][1]

[1]: http://ask.sqlservercentral.com/storage/temp/125-124-query.jpg
124-query.jpg (143.2 kB)
Feb 29, 2012 at 01:42 PM bandder
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Feb 29, 2012 at 02:07 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Feb 29, 2012 at 03:28 PM

bandder gravatar image

bandder
1 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x3

asked: Feb 28, 2012 at 08:12 AM

Seen: 1229 times

Last Updated: Feb 29, 2012 at 03:28 PM