GROUP BY/ HAVING CLAUSE problem

I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):

SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status = 1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)

..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.
?
.netsports

In caculations COUNT (* ) is the only aggregate function in SQL Server that caculates NULL values, so your results will be different if you use COUNT (* ) if any of you columns allow NULLs. Try the link below for more about SQL Server NULLs. Hope this helps.
http://www.akadia.com/services/dealing_with_null_values.html|||i am using four table(ForumMain,ForumThreads,ReplyToThread,Authentication) in my forum.I have 4 asp.net pages in this forum. On the very first page, I am showing the Main category of forums.i.e all forums,last thread posted,total threads so far and the total number of replies to each thead and of course the name of the user who generated or added last thread.
To do this, i am using count function to count the total replies to each thread,RepliesToThread table is doing that(not counting total threads yet),Forum Category field from the ForumMain table,ThreadName from the ForumThreads table and the username from the Authentication table.
I am using Group By clause as well but every time a new thread is added from AddThread.aspx page, the name of the main category which the new thread is added into, is repeated on the main page.
i.e. if I add a new thread in main category DATABASE, and this main category has already one thread, the main page show me like
DATABASE already existing category.
date:25/09/2005
DATABASE new category
date:26/09/2005
rather than it should show me
DATABASE new category
date:26/09/2005
What should I do to avoid this repetition?
Thanks in advance.|||Try the link below and see if GROUP BY with CUBE or ROLLUP operator will help with you problem and some restrictions apply. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp|||Caddre, this linke you providehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp is not providing help to solve my problem.
I am looking forward to more helpful replies from you or anybody else.|||Hi,
You're summingSale_Type_Id values from Orders table. I thinks this is not what you want to get. You can Count theSale_Type_Id values to have the number of orders.

SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS [foreign]
And if your records in Orders table have Sale_Type_Id values greaterthan 2, for each distinct value of Sale_Type_Id you'll get a differentrow.
Because you group your records due to Sale_Type_Id's. Note that if itis 2 or less. You group them as nulls. And remove only the null groupby using the Having clause.
So you still have groups having Sale_Type_Id's greater than 2
I hope it is helpfull
Eralper
http://www.eralper.com

GROUP BY / HAVING clauses problem

I'm trying to set up my adhoc query to return just one single record,
which is aliased as 'foreign' in my sql statement (which is just the
total amount of foreign overseas orders for just one day. All
Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING
clause, it will return three records, with one being blank.
?
.netsportsIf I understand correctly, your GROUP BY is on:
All Orders.Sale_Type_ID greater than 2
NULL
So, without the HAVING I would expect one row returned for each Sale_Type_ID
> 2 and one for all the rest which become NULL. Do you have two ID>2 within
the rows covered by your WHERE clause.
The HAVING apparently is able to prune out the NULL value.
Perhaps all you wanted was:
SELECT SUM(Orders.Sale_Type_Id) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =1) AND
Orders.Sale_Type_ID > 2
RLF
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1127848228.326875.56120@.g49g2000cwa.googlegroups.com...
> I'm trying to set up my adhoc query to return just one single record,
> which is aliased as 'foreign' in my sql statement (which is just the
> total amount of foreign overseas orders for just one day. All
> Sale_Type_Ids over 2 [integer datatype] are foreign orders):
> SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) AS foreign
> FROM Orders INNER JOIN
> Processing ON Orders.ID = Processing.Order_ID
> WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
> 1)
> GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
> ELSE NULL END
> HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) >= 0)
> ..but my resultset is returning two records. If I remove the HAVING
> clause, it will return three records, with one being blank.
> ?
> .netsports
>|||If you only want a single row then remove the GROUP BY clause. GROUP BY
returns one row per group.
David Portas
SQL Server MVP
--|||Thanks. Looks like i'm getting the desired resultset. This sql
statement was sort of a permutation of an extensive one that would
bring back multiple records, but yes, I was starting to think if the
Group By was really necessary.|||I'm inferring your schema to be something like this:
CREATE TABLE Orders
(
ID INT PRIMARY KEY ?
, Sale_Type_Id INT
, Order_Total MONEY ?maybe
)
CREATE TABLE Processing
(
OrderID INT REFERENCES Orders(OrderID)
, Orderdate DATETIME
, Status INT
)
Your question sounds like you're either trying to find a count of
orders, or total amount purchased (which is why I made up that
ordertotal field). If either of those are what you're looking for,
there are much syntactically simpler solutions.
e.g.
SELECT COUNT(*) CountOfForeignOrders
, SUM(Order_Total) TotalOfForeignOrders
FROM Orders
WHERE Sale_Type_Id > 2 AND EXISTS(SELECT * FROM Processing WHERE
OrderID = Orders.ID AND OrderDate = '20050915' AND Status = 1)
When performing aggregates with join clauses, it's possible to
aggregate the same value more than once if a join causes the same row
to appear multiple times in the resultset. GROUP BY and HAVING aren't
necessary unless you're selecting your data based on your aggregate,
and if you'd return multiple aggregate sets.
-Alan