This discussion is archived
1 Reply Latest reply: Apr 18, 2013 8:22 AM by Frank Kulash RSS

where clause on one column out of two

1000285 Newbie
Currently Being Moderated
I have a where clause filtering out data from the sum(workhour) column. I want to return another column beside it that does not filter workhour and a third column that filters workhour based on a different criteria. Is this possible?

sql

select activity_exit_date.year_id, activity_exit_date.month_id, locked_by, round(sum(workhour*60),1) as 'Maker/Checker Time'
from activity_exit_date
right join (project_name right join time_tracking on project_name.projectname_id=time_tracking.projectname_id)
on Activity_Exit_Date.Activity_Exit_Date=time_tracking.Activity_Exit_Date
where projectname like '%Maker%'
group by activity_exit_date.year_id, activity_exit_date.month_id, locked_by


year month locked_by maker/checker
2     2.0     aa93971     9316.2
2     2.0     ab41741     330.0
2     2.0     ab48799     8068.8
2     2.0     ag27219     7230.0
2     2.0     ah96254     7216.2
2     2.0     ah96763     6180.0
2     2.0     aj58427     1170.0
2     2.0     ak27324     7596.0
2     2.0     am37564     2790.0
2     2.0     ar04163     9885.0
2     2.0     as03789     9492.0
2     2.0     av04089     4854.0
2     2.0     aw03010     10056.6

thanks!

J
  • 1. Re: where clause on one column out of two
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Conditions in the WHERE clause affect the entire result set.

    If you want certain conitions only to apply to certain columns, then put the condition in a CASE expression.

    For example:
    SELECT       deptno
    ,       COUNT (*)          AS total_cnt
    ,       COUNT ( CASE
                     WHEN  job = 'CLERK'
                   THEN  1
                  END
                )          AS clerk_count
    ,       COUNT ( CASE
                     WHEN  sal > 1000
                   THEN  1
                  END
                )          AS sal_1000_cnt
    FROM      scott.emp
    GROUP BY  deptno;
    Output:
        DEPTNO  TOTAL_CNT CLERK_COUNT SAL_1000_CNT
    ---------- ---------- ----------- ------------
            30          6           1            5
            20          5           2            4
            10          3           1            3
    Note that these are the same numbers we would get by running these queries separately:
    SELECT  deptno, COUNT (*) FROM scott.emp                  GROUP BY deptno;
    SELECT  deptno, COUNT (*) FROM scott.emp WHERE job = 'CLERK' GROUP BY deptno;
    SELECT  deptno, COUNT (*) FROM scott.emp WHERE sal > 1000    GROUP BY deptno;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points