[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Summary Oracle SQL Script with different tables and WHERE criteria

Hello Experts,

I have an Oracle script that returns a list of users along with a total:

SELECT fname || ' ' || lname) user,
       count(*) total_submitted,
FROM table
WHERE submitted = 'Y' 
and trunc(date) between date1 and date2
group by fname || ' ' || lname)
order by fname || ' ' || lname)

Open in new window


I'm using VB.Net to tie these results into a asp:repeater. However, I need mutliple totals that come from different tables with different WHERE clauses for all users in one sql statement.

In MySql, I could do something like this:

SELECT first_name,
    last_name,
    ext,
    (SELECT count(*) FROM tbl WHERE whatever gets total1) AS total1,
    (SELECT count(*) FROM tbl WHERE whatever gets total2) AS total2,
    (SELECT count(*) FROM tbl WHERE whatever gets total3) AS total3,
    .......
FROM tbl_users
WHERE dept = deptid
AND date BETWEEN date1 AND date2
ORDER BY first_name
GROUP BY blah blah blah

Open in new window


I'm having trouble doing something like the above in Oracle.

Any suggestions?

Thanks in advance.
0
rmartes
Asked:
rmartes
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
It should work in Oracle.  What are the issues?

for example this works:
select 'hello' my_hello, (select count(*) from user_objects) obj_count from dual;
0
 
awking00Commented:
It's a little difficult to tell from your example, but perhaps something like the following is what you're looking for.

select t.first_name, t.last_name, t.ext,
t1.count(*)as tot1, t2.count(*) as tot2,t3.count(*) as tot3
from tbl_users t, tbl1 t1, tbl2 t2, tbl3 t3
where t.dept = t1.deptid
  and t.dept = t2.deptid
  and t.dept = t3.deptid
and <where conditions for t1>
and <where conditions for t2>
and <where conditions for t3>

Perhaps some sample data and the expected output might add some understanding of your requirements.
0
 
awking00Commented:
Would also need group by clauses.
0
 
lwadwellCommented:
Unlike MySQL ... in Oracle everything that isn't in aggregate (SUM(), COUNT(), MAX() etc) needs to be in the GROUP BY.  Your MySQL example can work in Oracle.

If you are not doing any aggregation in the SQL other than the sub-selects you do not need a group by ...
SELECT first_name,
    last_name,
    ext,
    (SELECT count(*) FROM tbl WHERE whatever gets total1) AS total1,
    (SELECT count(*) FROM tbl WHERE whatever gets total2) AS total2,
    (SELECT count(*) FROM tbl WHERE whatever gets total3) AS total3
FROM tbl_users
WHERE dept = deptid
AND date BETWEEN date1 AND date2
ORDER BY first_name

Open in new window


If you are doing other aggregates ... put the sub-selects into an aggregate ...
SELECT first_name,
    last_name,
    ext,
    MAX((SELECT count(*) FROM tbl WHERE whatever gets total1)) AS total1,
    MAX((SELECT count(*) FROM tbl WHERE whatever gets total2)) AS total2,
    MAX((SELECT count(*) FROM tbl WHERE whatever gets total3)) AS total3,
    count(*) -- other aggregate
FROM tbl_users
WHERE dept = deptid
AND date BETWEEN date1 AND date2
GROUP BY first_name, last_name, ext
ORDER BY first_name

Open in new window


If the sub-selects are correlated (a column in the outer select is used inside the WHERE of the sub-select like a join) then you can do them as in-line views...
SELECT first_name,
    last_name,
    ext,
    total1,
    total2,
    total3,
    count(*) -- other aggregate
FROM tbl_users
    JOIN (SELECT tbl.key, count(*) as total1
          FROM tbl 
          WHERE whatever gets total1
          GROUP BY tbl.key) v1 ON tbl_users.key = v1.key
    JOIN (SELECT tbl.key, count(*) as total2
          FROM tbl 
          WHERE whatever gets total2
          GROUP BY tbl.key) v2 ON tbl_users.key = v2.key
    JOIN (SELECT tbl.key, count(*) as total3
          FROM tbl 
          WHERE whatever gets total3
          GROUP BY tbl.key) v3 ON tbl_users.key = v3.key
WHERE dept = deptid
AND date BETWEEN date1 AND date2
GROUP BY first_name, last_name, ext, total1, total2, total3
ORDER BY first_name

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now