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

    Unanswered: GROUP BY and HAVING clause

    Hi

    Following query with Group By clause gives perfect result. I do not want
    Group by clause and when I remove Group by Clause, surprisingly, it counts
    all items (Count of Comm_Qty) and it does take care of the HAVING clause. So
    in short when I use Group by it take care of HAVING clause when I remove
    Group by it does not take care of HAVING clause.

    I want to remove Group By and still want the query to take care of Having clause.
    Can you please advise.

    SELECT Sum(IIf(COMM_QTY Is Not Null And DESP_QTY=COMM_qty,1,0)) AS Commited, Sum(IIf([DESP_QTY]=[COMM_QTY],1,0)) AS DESP
    FROM ORDERSSB
    WHERE (((ORDERSSB.PARTY_CODE)="JP1003") AND ((ORDERSSB.SHIP_NO) Like "2006*"))
    GROUP BY ORDERSSB.SHIP_NO
    HAVING (((Sum(IIf([DESP_QTY]=[COMM_QTY],1,0)))>0));

    Regards,
    Rakesh

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think your probalem is probably that the having clause in your first query is evaluated for EACH ship_no. In the second it is evaluated FOR ALL the data irrespective of ship_no.

    How about:
    Code:
    SELECT SUM(Commited_Sub) AS Commited, Sum(DESP_Sub) AS DESP
    FROM (
    SELECT Sum(IIf(COMM_QTY Is Not Null And DESP_QTY=COMM_qty,1,0)) AS Commited_Sub, Sum(IIf([DESP_QTY]=[COMM_QTY],1,0)) AS DESP_Sub
    FROM ORDERSSB
    WHERE ORDERSSB.PARTY_CODE="JP1003" AND ORDERSSB.SHIP_NO Like "2006*"
    GROUP BY ORDERSSB.SHIP_NO
    HAVING Sum(IIf([DESP_QTY]=[COMM_QTY],1,0))>0) AS DerT
    ??
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2006
    Posts
    20

    Group By And Having Clause

    Hi

    Following query with Group By clause gives perfect result. I do not want
    Group by clause and when I remove Group by Clause, surprisingly, it counts
    all items (Count of Comm_Qty) and it does take care of the HAVING clause. So
    in short when I use Group by it take care of HAVING clause when I remove
    Group by it does not take care of HAVING clause.

    I want to remove Group By and still want the query to take care of Having clause.
    Can you please advise.

    SELECT Sum(IIf(COMM_QTY Is Not Null And DESP_QTY=COMM_qty,1,0)) AS Commited, Sum(IIf([DESP_QTY]=[COMM_QTY],1,0)) AS DESP
    FROM ORDERSSB
    WHERE (((ORDERSSB.PARTY_CODE)="JP1003") AND ((ORDERSSB.SHIP_NO) Like "2006*"))
    GROUP BY ORDERSSB.SHIP_NO
    HAVING (((Sum(IIf([DESP_QTY]=[COMM_QTY],1,0)))>0));

    Regards,
    Rakesh

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your statements are contradictory

    first you say "when I remove Group by Clause, surprisingly, ... it does take care of the HAVING clause"

    then in the next sentence you say "when I remove Group by it does not take care of HAVING clause"

    anyhow, let's stop and think about this for a second

    without a GROUP BY clause, the entire table is treated as a single group

    (the most familiar example of this is select count(*) from daTable)

    so without a GROUP BY clause, the query will produce exactly one result row, and if you leave the HAVING clause, then only two things can happen -- either the HAVING clause is satisfied, in which case you get that one row, or the HAVING clause is not satisfied, in which case you get no result rows at all

    okay, what was your question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please don't post the same question into two different forums

    they will often be merged together, which can be confusing if people had posted answers in the separate threads
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2006
    Posts
    20
    Quote Originally Posted by r937
    your statements are contradictory

    first you say "when I remove Group by Clause, surprisingly, ... it does take care of the HAVING clause"

    then in the next sentence you say "when I remove Group by it does not take care of HAVING clause"

    anyhow, let's stop and think about this for a second

    without a GROUP BY clause, the entire table is treated as a single group

    (the most familiar example of this is select count(*) from daTable)

    so without a GROUP BY clause, the query will produce exactly one result row, and if you leave the HAVING clause, then only two things can happen -- either the HAVING clause is satisfied, in which case you get that one row, or the HAVING clause is not satisfied, in which case you get no result rows at all

    okay, what was your question again?
    Dear Sir,

    Thanks for the prompt response. I have two queries. First one to get 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
    ------------------------------------------------------------------

    Above first query works fine and I have no question there.

    Second query: Now with the same first query I want the average of each
    of promised orderlines, achieved orderlines. For that,
    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

  7. #7
    Join Date
    May 2006
    Posts
    20
    Dear Sir,

    Further to my above post, I clarify your question as under:

    When I use having clause to show only none zero orderlines it
    works perfectly with Group by. Like;

    Orderlines-----Shipped Orderlines**
    250------------240
    246------------236
    230------------230

    (**Having shipped orderlines > 0)

    I want the same result but WITHOUT Group by means, only one row
    giving sum total of the records returned i.e 726 (250+246+230).
    The problem here is that without Group by, the HAVING clause is
    totally ignored and it sums up and shows all orderlines instead of
    summing up only orderlines > 0. Likes

    Promised orderlines-----Shipped ORderlines

    Orderlines-----Shipped Orderlines**
    250------------240
    246------------236
    230------------230
    200------------0 -- Should not be considered for summing up
    210------------0 -- Should not be considered for summing up

    The result I get is;

    Orderlines-----Shipped orderlines
    1136 (250+246+230+200+210-----------706 (240+236+230)

    The result I want is;
    --------------------

    Orderlines-----Shipped orderlines
    726 (250+246+230)-----------706 (240+236+230)

    When I use Group by it gives 250+246+230 orderlines and when I
    remove Group by it should give only 726 but it gives 1136 means
    it does not consider Having clause.

    I hope the requirement is very clear now.

    Thanks,
    Rakesh

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select sum(Promised_OrderLines) as sum_Promised_OrderLines
         , sum(Achieved_OrderLines) as sum_Achieved_OrderLines
         , sum(Promised_OrderLines) * 100
          /sum(Achieved_OrderLines) as Reliability
      from (
           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
           ) as t
     where Achieved_OrderLines > 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2006
    Posts
    20
    Dear Sir,

    Thank you very much for the code. I knew lots of new things from this
    query. The final query that worked is as under. I used HAVING to restrict
    none-zero records before summing up by new table.

    SELECT SUM(ORDERLINE) AS PROMISED,
    SUM(ACHIEVED) AS ACHIEVEMENTS,
    ROUND(SUM(ACHIEVED*100) / SUM(ORDERLINE),0) AS RELIABILITY
    FROM (
    SELECT COUNT(comm_qty) AS ORDERLINE,
    SUM(IIF(DESP_QTY = COMM_QTY, 1, 0)) AS ACHIEVED
    FROM ORDERSSB
    INNER JOIN ITEMMST ON ORDERSSB.ITEM_CODE=ITEMMST.ITEM_CODE
    WHERE (ORDERSSB.party_code = 'JP1003') AND (ITEMMST.party_code = 'JP1003')
    AND (ship_no LIKE '2006*') AND ITEMMST.DEVELOPMNT = 'N'
    GROUP BY SHIP_NO
    HAVING SUM(IIF(DESP_QTY = COMM_QTY, 1, 0)) > 0
    )
    AS NEWTABLE

    Sir, thanks a lot again,
    Rakesh

  10. #10
    Join Date
    May 2006
    Posts
    20
    Dear Pootle,

    Thank you very much for the advice. It took me some time
    before I could understand your code as I am new to this.
    I am very sorry for the delay in my acknowlegement to your
    reply.

    Again, thanks a lot for the code.
    Rakesh

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rakeshprkh
    Dear Pootle,

    Thank you very much for the advice. It took me some time
    before I could understand your code as I am new to this.
    I am very sorry for the delay in my acknowlegement to your
    reply.

    Again, thanks a lot for the code.
    Rakesh
    My pleasure and no probs
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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