# Thread: Group By And Having Clause

1. Registered User
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.

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
HAVING (((Sum(IIf([DESP_QTY]=[COMM_QTY],1,0)))>0));

Regards,
Rakesh

2. King of Understatement
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.

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
HAVING Sum(IIf([DESP_QTY]=[COMM_QTY],1,0))>0) AS DerT```
??

3. Registered User
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.

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
HAVING (((Sum(IIf([DESP_QTY]=[COMM_QTY],1,0)))>0));

Regards,
Rakesh

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

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"

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?

5. SQL Consultant
Join Date
Apr 2002
Location
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

6. Registered User
Join Date
May 2006
Posts
20
Originally Posted by r937

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"

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
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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
AND (ITEMMST.party_code = "JP1003")
AND (ship_no LIKE "2006????")
AND ITEMMST.ISDEVELOPED = "N"
GROUP BY SHIPMENT_NO
) as t
where Achieved_OrderLines > 0```

9. Registered User
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
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. Registered User
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

Again, thanks a lot for the code.
Rakesh

11. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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