Grouped by Using aliased names

Hi,

I have a select statement that gives me an output as follows:

Date Store Num Location
4-5-2007 0001 NY
4-5-2007 0002 NY
4-5-2007 0002 NY
4-4-2007 0003 PA
4-4-2007 0002 PA

The store num and location columns are derived like so:
LEFT(Table.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(Table.WholeField, 6), 2) AS 'Location'

The problem I am running into is that I have been tasked to write a select statement that sums up distinct values for Store Num and Location. The output should look something like this :

Date Store Num Location Num
4-5-2007 0002 NY 2

However, 'Store Num' and 'Location' comes from one field by design. I have written a select statement that uses the GROUP BY function to get the correct output. However, I am receiving an invalid column name error because I am using an aliased name.

Does anyone have any insight into the error or a possible workaround.

Thanks,
V.Don't use the alias in the Group By, use the expression instead.

GROUP BY LEFT(Table.WholeField, 4), RIGHT(LEFT(Table.WholeField, 6), 2)

or just bury the original query as a subquery, and then sum and group by.

The first version seems "cleaner" to me though|||Thanks for the quick response. The Grouping by for the expressions works.

However, I am still getting:

Date Store Num Location Num
4-5-2007 0002 NY 1
4-5-2007 0002 NY 1

However, I am looking for :

Date Store Num Location Num
4-5-2007 0002 NY 2

Shouldn't the group by statement work with a correct count(*) or do I have to issue counts for the two separate columns?|||Can you post the SQL statement?|||Actually, I figured it out. Thanks for your help! Not thinking straight for some reason today!|||I don't see why this wouldn't work

SELECT [DATE],
LEFT(#TMP.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(#TMP.WholeField, 6), 2) AS 'Location',
COUNT(*)
FROM #TMP
GROUP BY [DATE],
LEFT(#TMP.WholeField, 4),
RIGHT(LEFT(#TMP.WholeField, 6), 2)

That yields

Date Store Num Location Num
4-4-2007 0002 PA 1
4-4-2007 0003 PA 1
4-5-2007 0001 NY 1
4-5-2007 0002 NY 2|||I was using a convert function to take the timestamp field Date and convert it to a MM-DD-YYYY format. However, in the group by statement i was using just the Fieldname Date. When I added the convert function to the group by, it worked.

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