Home » SQL & PL/SQL » SQL & PL/SQL » Sql Querry based on condition
Sql Querry based on condition [message #629518] Wed, 10 December 2014 06:30 Go to next message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
Hi,


I have table like below.I need count of typeofemployees and its aggrigated value like below shown in output.
1)While taking counts and aggregating values rwe will consider the emptype should be both permanent and contract for the particular empid.
2)If in case if any particular empiid belongs to contract emptype
then it should not display in myoutput.
3)If any empid have only permanent then it will consider that record to display in output.


Please see output result for clarification...

table:
Country firstsdate lastdate emp_id dept_id TypeofEmployee Val
US 1-Jan-14 31-Jan-14 1 10 Contract 10
US 1-Jan-14 31-Jan-14 1 11 Contract 20
US 1-Jan-14 31-Jan-14 1 11 Permanent 20
US 1-Jan-14 31-Jan-14 1 12 Permanent 30
US 1-Jan-14 31-Jan-14 2 20 Permanent 40
US 1-Jan-14 31-Jan-14 3 30 Contract 50
US 1-Jan-14 31-Jan-14 3 31 Permanent 60
US 1-Jan-14 31-Jan-14 3 32 Permanent 70
US 1-Jan-14 31-Jan-14 4 41 Contract 80
Output:
country firstsdate lastdate emp_id Permcount Contcount Permvalu ConValue
US 1-Jan-14 31-Jan-14 1 2 1 50 30
US 1-Jan-14 31-Jan-14 2 1 0 40 0
US 1-Jan-14 31-Jan-14 3 2 1 130 50

Re: Sql Querry based on condition [message #629533 is a reply to message #629518] Wed, 10 December 2014 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Quote:
2)If in case if any particular empiid belongs to contract emptype then it should not display in myoutput.
3)If any empid have only permanent then it will consider that record to display in output.

Input contains:
US 1-Jan-14 31-Jan-14 1 10 Contract 10
Result contains:
US 1-Jan-14 31-Jan-14 1 2 1 50 30
Why empid 1 is present in output when it contains "contract"?

You have to better explain the rules and your output, above all the new columns.
You have to tell us on what you aggregate. Empid alone? Empid+Country+firstdate+lastdate as your output shows it?

Re: Sql Querry based on condition [message #630895 is a reply to message #629533] Tue, 06 January 2015 03:03 Go to previous message
asbh123
Messages: 26
Registered: November 2014
Location: mumbai
Junior Member
Hi,

The below one is the script worked for me...
select country,firstsdate,lastdate,emp_id,dept_id,typeofemployee,Val,

       count(case typeofemployee when 'Permanent' then 1 end) over (partition by emp_id) permanent_count,

       count(case typeofemployee when 'Contract' then 1 end) over (partition by emp_id) contract_count,

       sum(case typeofemployee when 'Permanent' then val end) over (partition by emp_id) permanent_value,

       sum(case typeofemployee when 'Contract' then val end) over (partition by emp_id) contract_value,

       row_number() over (partition by emp_id order by null) rn

  from employee
Previous Topic: Insert Query in between rows - SQL
Next Topic: Find a column Name using data
Goto Forum:
  


Current Time: Thu Mar 28 09:33:07 CDT 2024