views:

37

answers:

2

hi! i have 3 tables A,B and C.

  • table A has column employee_name,id
  • table B is the main table and has columns id,os version.
  • table c has the columns id,package id and p_version.

I want to query the count of employee_name where the id of table a and c are matched with id of table b(which is the main table).

I should also get the names of employees grouped by the os version they have and also the p_version.

i have tried,

select count(employee_name),os.version,p_version where a.id=b.id and b.id=c.id;

i want the count of employee names to be grouped by os.version and p_version.

A: 

I'm not too certain I understand your requirements .. the following is a blind guess.

I'm not too certain how table c should be connected to table b, btw .. somehow the schema doesn't make alot of sense to me!

select  b.os
     , c.package_version
     , count(a.employee_name)
from B
  join A  on (b.id=a.id)
  join C  on (b.id=c.id)
group by b.os,c.package_version
lexu
its giving ora-00936 error.
randeepsp
ora-00936 (missing expression) by itself is meaningless ... what else does it say?
lexu
A: 

(The question still seems unclear to me, but I'll make an attempt:)

If you have tried

select count(employee_name),os.version,p_version where a.id=b.id and b.id=c.id;

then I assume that is because it correctly addresses the schema. So I would try this instead:

select "os version", p_version, count(employee_name) 
from a,b,c 
where a.id=b.id and b.id=c.id
group by "os version", p_version;

One unclear item is your question is "os version." You presented it with a space, so I am going to guess that it has a space in its name. Normally that does not happen, but if it is true, then you would have to somehow quote the column name. My solution would probably address the problem in oracle. If it actually has an underscore or something, then ignore the quotes.

MJB