0

I have a EMP table. I need to get number of employees in each department grouped by country name = 'INDIA','USA', 'AUSTRALIA'.

For example, 

DEPARTMENT   | #EMPLOYEE(INDIA) | #EMPLOYEE(USA) | # EMPLOYEE(AUSTRALIA)    
ACCOUNTING   | 5                |2               | 3
IT           | 5                |2               | 1
BUSINESS     | 1                |4               | 3

I need to use Partition BY to do it. I am able to use PARTITION by to get the total count of employees for each department. But I am not able to subgroup by country name.

Please give me suggestions.

Thank you.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Roshni
  • 189
  • 1
  • 18
  • That is the example of current table or desire output? we need both. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Nov 30 '16 at 21:20
  • That is the desired output. Thank you. Current table has Employee ID, Name, Country, Department, Salary. – Roshni Nov 30 '16 at 21:22
  • No, you do not need to use "partition by" anything. What you do need is to get the result in your requirement; how you do that is not required. Besides, in this case "partition by" would not help anyway. What you need is `PIVOT` - except that if you don't know the number and names (and desired order) of all countries ahead of time, you will not be able to do this in "regular" SQL, you will need **dynamic** SQL. –  Nov 30 '16 at 21:26
  • @mathguy after reading again `need to use Partition BY` sound more like homework. – Juan Carlos Oropeza Nov 30 '16 at 21:29

1 Answers1

0

Consider conditional count.

SELECT  DEPARTMENT,
        COUNT(CASE WHEN Country = 'INDIA' THEN 1 END) as emp_india,
        COUNT(CASE WHEN Country = 'USA' THEN 1 END) as emp_usa,
        COUNT(CASE WHEN Country = 'AUSTRALIA' THEN 1 END) as emp_australia
GROUP BY DEPARTMENT
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • With the caution that this approach only works if the number of countries, their names and their order are given ahead of time. Otherwise dynamic SQL can't be avoided. (Unless one chooses to avoid the de-normalized output in the first place, or has the good sense to use reporting tools for this task, rather than SQL.) –  Nov 30 '16 at 21:28
  • @mathguy I agree, but the countries are know here. Also why you say country order has anything to do here? – Juan Carlos Oropeza Nov 30 '16 at 21:30
  • I am not sure about countries being known (or departments for that matter). Maybe they are, or maybe the OP showed only a representative set of data. Order: is the order in which the columns will appear. Presumably they should appear in *some* order, such as alphabetically or by number of total employees or who knows by what other rule or set of rules. Businesses have their weird requirements... –  Nov 30 '16 at 21:34
  • @JuanCarlosOropeza Thank you for your prompt reply. It works like charm. However, the data is huge and hence, it is taking a bit long time. Perhaps this is why I was told to use Partition. – Roshni Nov 30 '16 at 21:35
  • Do you have index on `country` and `department` ? – Juan Carlos Oropeza Nov 30 '16 at 21:39
  • And, to clarify: If the countries must appear alphabetically, that is known beforehand and is not a problem; but if the columns (countries) must appear by total number of employees in each country, from left to right, then that still requires dynamic SQL, even if the number and names of countries are known beforehand. –  Nov 30 '16 at 21:44
  • No..there is no index. I am querying from a view. – Roshni Nov 30 '16 at 21:46
  • maybe consider materialized view http://stackoverflow.com/questions/6531564/index-on-view-oracle – Juan Carlos Oropeza Nov 30 '16 at 22:06