Results 1 to 12 of 12
  1. #1
    Join Date
    May 2006
    Posts
    20

    Unanswered: Can I use two WHERE clauses, one common, one specific

    Hi,

    Can I use two WHERE clauses, one common for all fields and one
    WHERE clause only for one of the fields like

    SELECT SHIP_NO, Count(ORDER_QTY) AS ORDR, (SELECT Count(SHIPPED_QTY) AS SHIP
    FROM ORDERS
    WHERE (ORDER_NO Like "2006A??") Or
    (ORDER_NO Like "2006L??") AND
    (SHIPPED_QTY)=[ORDER_QTY]))

    FROM ORDERSSB
    WHERE (ORDER_NO Like "2006A??") Or
    (ORDER_NO Like "2006L??")
    GROUP BY SHIP_NO;

    I want to count order_qty which is not a problem. The problem is with counting of shipped_qty
    where I want to count only those records where shipped_qty is equal to order_qty because shipped
    qty maybe also be less than the order_qty or maybe zero. So I want count of only those
    shipped_qty where the qty is same as order_qty.

    I used a as above subquery to count shipped_qty but it says "At most one record can be
    returned by this subquery".

    Please help and advise.

    Thanks,
    Rakesh

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you select a count in the subquery like that, the subquery will return only one row, so i don't understand how you got the error message

    the alias should go outside the subquery
    Code:
    SELECT SHIP_NO
         , Count(ORDER_QTY) AS ORDR
         , ( SELECT Count(SHIPPED_QTY)
               FROM ORDERS
              WHERE (ORDER_NO Like "2006A??") 
                 Or (ORDER_NO Like "2006L??") 
                AND (SHIPPED_QTY)=[ORDER_QTY]) ) as SHIP
      FROM ORDERSSB
     WHERE (ORDER_NO Like "2006A??") 
        Or (ORDER_NO Like "2006L??")
    GROUP 
        BY SHIP_NO
    there might be a problem with your ORs and ANDs in the subquery, because the AND (SHIPPED_QTY)=[ORDER_QTY]) will attach only to the immediately preceeding condition, (ORDER_NO Like "2006L??")

    there's also another problem, because your subquery will return the count for all SHIP_NOs, and not just the individual SHIP_NO in the outer query

    there's a much simpler way to do what you want --
    Code:
    SELECT SHIP_NO
         , Count(ORDER_QTY) AS ORDR
         , sum( case when SHIPPED_QTY = ORDER_QTY
                     then 1 else 0 end ) as SHIP
      FROM ORDERSSB
     WHERE (ORDER_NO Like "2006A??") 
        Or (ORDER_NO Like "2006L??")
    GROUP 
        BY SHIP_NO
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Operative word being think, I think that your problem is that you haven't correlated your sub-query. In other words your existing sub-query is returning all of the rows where order matches shipped, not just the rows for the order in the outer query. If my understanding of the problem is correct, then you should probably use:
    Code:
    SELECT SHIP_NO, Count(ORDER_QTY) AS ORDR
    ,  (SELECT Count(SHIPPED_QTY) AS SHIP
          FROM ORDERS
          WHERE (ORDERS.ORDER_NO = ORDERSSB.ORDER_NO
             AND (SHIPPED_QTY)=[ORDER_QTY]))
       FROM ORDERSSB
       WHERE (ORDER_NO Like "2006A??")
          OR (ORDER_NO Like "2006L??")
       GROUP BY SHIP_NO;
    -PatP

  4. #4
    Join Date
    May 2006
    Posts
    20
    Dear Pat,

    Thank you for the quick response however, there is nothing new in
    the query that you suggested because it gives the same result
    as what I have used original. The result is as under:

    SHIP_NO ORDER_QTY SHIPPED_QTY
    20060012 226 8104
    20060014 206 8104
    20060027 240 8104
    20060028 274 8104
    20060030 262 8104
    20060032 263 8104

    The ORDER_QTY count is correct. However, The SHIPPED_QTY count is incorrect. What the
    query shows is the total count for all SHIP_NO instead of a particular SHIP_NO. I expect
    the query to give result somthing like as under:


    SHIP_NO ORDER_QTY SHIPPED_QTY
    20060012 226 220
    20060014 206 206
    20060027 240 235
    20060028 274 270
    20060030 262 261
    20060032 263 258

    I checked the subquery separaely and just works fine alone but when put with
    ORDER_QRY it gives total count. Please help and advise.

    Kind regards,
    Rakesh

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try my query?

    the second one, the much simpler way to do what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2006
    Posts
    20

    Can I use two Clauses, one common one specific

    Dear r937,

    I was just trying to reply to your answer to my query but I could not see any button
    to reply to you. It was showing only button below the answer from Pat pointing reply to
    Pat only. Anyway, first of all I thank you for your quick response. However, I am not
    able to understand the following code you mentioned in your reply.

    sum(case when SHIPPED_QTY = ORDER_QTY
    then 1 else 0 end ) as SHIP

    I use Microsoft Access. I understand that it is not the actual code but it's guideline however,
    I would request you if you can kindly put this code in a actual query so that
    I can use it as it is. From my answer to Pat, I hope you understand the result I want from
    the query. Kindly help and advise.

    Kind regards,
    Rakesh

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use this instead --
    Code:
    SUM(IIF(SHIPPED_QTY=ORDER_QTY,1,0)) as SHIP
    and i will move this thread to the Access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2006
    Posts
    20

    Can I use two WHERE.........

    Dear R937,

    You're greater than the greatest and I am happier than the happiest.
    I spent more than 10 days on this so so simple query. It worked in a fraction of a second.

    I thank you very much again and again. I now realised that the world has become so small and
    we should not live with the problem anymore.

    Thank you again,
    Rakesh

  9. #9
    Join Date
    May 2006
    Posts
    20
    SELECT COUNT(promised_qty) AS Promised_OrderLines
    SUM(IIF(shipped_QTY = promised_qty, 1, 0)) AS Achieved_OrderLines
    ROUND(SUM(IIF(shipped_qty = promised_qty, 1, 0)) / COUNT (promised_qty) * 100, 0) AS Reliability
    FROM ORDERS INNER JOIN ITEMMST ON ORDERS.ITEM_CODE=ITEMMST.ITEM_CODE
    WHERE (ORDERSSB.party_code = "JP1003")
    AND (ITEMMST.party_code = "JP1003")
    AND (ship_no LIKE "2006????")
    AND ITEMMST.ISDEVELOPED = "N"
    GROUP BY SHIPMENT_NO

    The result I get is like

    Promised_OrderLines-----Achieved_OrderLines-----Reliability (%)
    195-----182-----93
    206-----195-----95
    226-----214-----95
    240-----229-----95
    247-----0-------0
    263-----0-------0
    251-----0-------0

    Above result is fine as I want. Now I removed 'Group by SHIPMENT_NO' from abvoe query
    to get total promised_OrderLines and total Achieved_OrderLines to calculate average
    delivery reliability. While getting total orderlines both promised and achieved,
    I want total orderlines of only following where orderlines are greater than zero.

    195-----182-----93
    206-----195-----95
    226-----214-----95
    240-----229-----95
    -------------------
    867-----820-----94 <----I want to arrive at this figure
    ===================

    So the final result will be sum total of promised and achieved orderlines and thus
    delivery reliability. How do I tell query to sum total only those records where
    achieved_OrderLines > than 0. With my various attempts I could do this but it only
    works for the sum total of Achieved_OrderLInes (without zero) but the problem is that in
    case of promised_orderlines it does sum total of all orderlines including those of zero
    achieved_OrderLines.

    Can anybody please help me on this?

    Kind regards,
    Rakesh

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  SUM(IIF(promised_qty IS NOT NULL AND shipped_QTY = promised_qty, 1, 0)) AS Promised_OrderLines,
            SUM(IIF(shipped_QTY = promised_qty, 1, 0)) AS Achieved_OrderLines,
            ROUND(SUM(IIf(shipped_QTY = promised_qty, 1, 0)) / Count(promised_qty) * 100, 0) As Reliability
    FROM ORDERS
                INNER JOIN ITEMMST ON
            ORDERS.ITEM_CODE = ITEMMST.ITEM_CODE
    WHERE ORDERSSB.party_code = "JP1003"
            AND ITEMMST.party_code = "JP1003"
            AND ship_no LIKE "2006????"
            AND ITEMMST.ISDEVELOPED = "N"
    Really just an extension of the same logic.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or you could use a having clause:
    Code:
    SELECT COUNT(promised_qty) AS Promised_OrderLines
    SUM(IIF(shipped_QTY = promised_qty, 1, 0)) AS Achieved_OrderLines 
    ROUND(SUM(IIF(shipped_qty = promised_qty, 1, 0)) / COUNT (promised_qty) * 100, 0) AS Reliability
    FROM ORDERS INNER JOIN ITEMMST ON ORDERS.ITEM_CODE=ITEMMST.ITEM_CODE
    WHERE (ORDERSSB.party_code = "JP1003") 
    AND (ITEMMST.party_code = "JP1003")
    AND (ship_no LIKE "2006????") 
    AND ITEMMST.ISDEVELOPED = "N"
    HAVING SUM(IIF(shipped_QTY = promised_qty, 1, 0)) >0
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2006
    Posts
    20

    Problem with Counts

    Dear pootle flump,

    Thanks for the prompt response. However none of the two queries you
    advised work as I want. I already have tried Having clause earlier but
    of no use. The second query (other than HAVING clause) does not give
    correct result because the conditions are not so:

    FOr getting the average reliability I want to count promised_qty of
    all shipments other than the shipment which is yet to be shipped means where
    the shipped_qty is greater than zero.

    I need to get following break-up of each shipment with this query:

    SELECT COUNT(promised_qty) AS Promised_OrderLines
    SUM(IIF(shipped_QTY = promised_qty, 1, 0)) AS Achieved_OrderLines
    ROUND(SUM(IIF(shipped_qty = promised_qty, 1, 0)) / COUNT (promised_qty) * 100, 0) AS Reliability
    FROM ORDERS INNER JOIN ITEMMST ON ORDERS.ITEM_CODE=ITEMMST.ITEM_CODE
    WHERE (ORDERSSB.party_code = "JP1003")
    AND (ITEMMST.party_code = "JP1003")
    AND (ship_no LIKE "2006????")
    AND ITEMMST.ISDEVELOPED = "N"
    GROUP BY SHIPMENT_NO

    Above query gives following result (FIRST COLUMN (SR) IS NOT PART OF QUERY)
    -------------------------------------------------------------------
    SR Promised_OrderLines-----Achieved_OrderLines-----Reliability (%)
    -------------------------------------------------------------------
    A 195-----182-----93
    B 206-----195-----95
    C 226-----214-----95
    D 240-----229-----95
    E 247-----0-------0
    F 263-----0-------0
    G 251-----0-------0
    ------------------------------------------------------------------

    THE NEW QUERY (WHERE I HAVE PROBLEM AND I NEED YOUR HELP) IS AS UNDER:

    Now to get the average reliability I remove group by clause. Also
    I want to calculate average only where achieved_orderLines are greater
    than zero. That is as under:

    SR Promised_OrderLines-----Achieved_OrderLines-----Reliability (%)
    A 195-----182-----93
    B 206-----195-----95
    C 226-----214-----95
    D 240-----229-----95 <--- the query should sum total till this row (up to D)
    E 247-----0-------0
    F 263-----0-------0
    G 251-----0-------0

    tHE query should give following result (sum total of A to D, each column) because after
    D, the E row has 0 (zero) achieved_orderLInes.

    -------------------
    867-----820-----94 <----I want to arrive at this figure
    ===================

    Promised order lines are always higher than achieved orderlines
    Achieved order lines could be zero,less than or equal to promised orderlines

    I can restrict achieved orderlines where they are equal to zero. However, the
    problem is with achieved ordelines where it count all shipments instead of
    just those rows till (D) in above example. Please have a fresh look again and
    help me.

    Thank you,
    Rakesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •