optimize sql

Posted on 2010-01-06
Last Modified: 2013-12-19
Can I make these two subqueries into one?

select a.status_desc, a.dept_name, sku_count, fcr_count from
(select status_desc,
             count(distinct fpo_no || style || colour) sku_count
      from agi_016a_t1
      group by status_desc, dept_name) a
left join
(select status_desc,
              count(distinct fpo_no || style || colour) fcr_count
       from agi_016a_t1
       where fcr = 1  
       group by status_desc, dept_name) b on b.status_desc = a.status_desc and b.dept_name = a.dept_name;
Question by:orauser
    LVL 26

    Accepted Solution

    TRY TH0S
    select a.status_desc, a.dept_name, COUNT(*) AS sku_count, SUM(CASE WHEN fcr = 1 THEN 1 ELSE 0 END) AS fcr_count 
    from agi_016a_t1
    group by status_desc, dept_name

    Open in new window


    Author Closing Comment

    Can adjust the temp table to contain distinct fpo/style/colour instead of doing it here

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now