Group column name problem

How would you do this statement:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Description,Price,PurchaseQty,TotalPrice
The problem is I get an "Invalid Column Name" for Description and
TotalPrice.
I assume that is because the names are assigned.
How can I make this work?
Thanks,
Tomgroup by the expression, e.g.
...group by Case when ProductTypeID = 1 then j.ItemName when
ProductTypeID = 2 then
r.ItemName end,
Price, PurchaseQty,
Price * PurchaseQty
tshad wrote:

>How would you do this statement:
>Select rank=count(*),
> Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
>r.ItemName end as Description,
> Price, PurchaseQty, TotalPrice = Price * PurchaseQty
>from PurchaseDetail pd
>join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
>left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
>left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
>where CompanyID = 153973
>group by Description,Price,PurchaseQty,TotalPrice
>The problem is I get an "Invalid Column Name" for Description and
>TotalPrice.
>I assume that is because the names are assigned.
>How can I make this work?
>Thanks,
>Tom
>
>|||"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eAwOKUa2FHA.3600@.TK2MSFTNGP12.phx.gbl...
> group by the expression, e.g.
> ...group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID
> = 2 then
> r.ItemName end,
> Price, PurchaseQty,
> Price * PurchaseQty
>
I was hoping I wouldn't have to do that. That would mean in my larger
scripts that use large Case statements would also have to be put in the
Group by clause.
Thanks,
Tom
> tshad wrote:
>|||On Wed, 26 Oct 2005 16:36:03 -0700, tshad wrote:

>"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
>news:eAwOKUa2FHA.3600@.TK2MSFTNGP12.phx.gbl...
>I was hoping I wouldn't have to do that. That would mean in my larger
>scripts that use large Case statements would also have to be put in the
>Group by clause.
Hi Tom,
There are two workarounds:
1. Instead of including the expression in the GROUP BY, include ALL
columns used in the expression. I've never checked if ANSI standard
allows it, but AFAIK, SQL Server does.
2. Use a derived table:
SELECT result, MAX(something else)
FROM (SELECT complicated expression AS result,
something else
FROM some tables
WHERE whatever you want) AS Der
GROUP BY result
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p5g2m1pigre1nfievlll39mfp7nh9jdd94@.
4ax.com...
> On Wed, 26 Oct 2005 16:36:03 -0700, tshad wrote:
>
ProductTypeID
> Hi Tom,
> There are two workarounds:
> 1. Instead of including the expression in the GROUP BY, include ALL
> columns used in the expression. I've never checked if ANSI standard
> allows it, but AFAIK, SQL Server does.
Haven't tried that yet, but wouldn't the grouping be incorrect as you are
looking at the value of the column instead of the derived value of that
expression from the column?
Not really sure why you can't use the assigned title of the column( x as
column).

> 2. Use a derived table:
> SELECT result, MAX(something else)
> FROM (SELECT complicated expression AS result,
> something else
> FROM some tables
> WHERE whatever you want) AS Der
> GROUP BY result
That was also what Peter suggested, which worked in this example.
I also looked at setting up views as we did before in my other problem, but
it seemed like overkill in this problem. I am just trying figure out at
what point I would use this type of solution. What is the clue that tells
you that the best solution is by wrapping one select statement inside of
another select statement.
Thanks,
Tom
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 28 Oct 2005 02:40:20 -0700, tshad wrote:
(snip)
>Haven't tried that yet, but wouldn't the grouping be incorrect as you are
>looking at the value of the column instead of the derived value of that
>expression from the column?
Hi Tom,
Yes, you're right. I forgot that. GROUP BY all columns used in the
expresion makes for a valid query, but it'll only produce the same
results if no two sets of column values can ever result in the same
result of the expression.

>Not really sure why you can't use the assigned title of the column( x as
>column).
This has to do with the "official" way to process a SELECT. Official,
bacuase that's how ANSI says it should be done. In quotes, because all
major databases will choose other orders to optimize for speed, as long
as the results are the same as if the official order had been used.
Step 1: Process FROM clause (includes all JOIN clauses). Results in a
temporary table (stored internally) that holds all columns of all tables
used in the FROM clause, with all rows that satisfy the JOIN conditions.
If old-style FROM cluase is used (i.e. FROM table1, table2, ...), this
will hold the Carthesian product of the tables.
Step 2: Process WHERE clause. Check WHERE clause for each row in temp
table from step 1, and remove row if WHERE clause evaluates to FALSE or
UNKNOWN.
Step 3: Process GROUP BY clause. Using table from step 2, form groups of
rows that share the same value for all columns (or expressions) in the
GROUP BY clause.
Step 4: Process HAVING clause. Check HAVING clause for each *group of
rows* in the temp table after step 3, and remove *complete group* if
HAVING clause evaluates to FALSE or UNKNOWN.
Step 5: Process SELECT clause. Result of this step will be a table with
one column for each entry in the SELECT clause. If no GROUP BY is
present, than result set will have one row for each row left in the temp
table. If a GROUP BY is present, than result set will have one row for
each *group* of rows left in the temp table, and expression in the
SELECT list can't refer to columns/expressions not in the GROUP BY list,
unless enclosed in an aggregate function.
Since the SELECT is processed last, neither the result of the expression
nor the column alias given to it is available when the GROUP BY is
processed.

>That was also what Peter suggested, which worked in this example.
I don't see a message by Peter - had I known that you've already been
given this advise, I wouldn't have repeated it. Was Peter's reply in
this thread?

>I also looked at setting up views as we did before in my other problem, but
>it seemed like overkill in this problem. I am just trying figure out at
>what point I would use this type of solution. What is the clue that tells
>you that the best solution is by wrapping one select statement inside of
>another select statement.
In situations like this, I use a derived table if I have to repeat a
complicated expression. If I also might want to use the same expression
in other queries, I might go for a view. If it's this query only, I
prefer a derived table. If the expression is fairly simple, I just
repeat it.
Those are the rules of thumb. Readability and maintainability of code
are very important too, of course. And for the final decision, at least
when it's in code that needs to be fast, you'll have to test ... test
... test.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hc25m1tmpkh1tdrr17n3oi2fp0e0btpqb5@.
4ax.com...
> On Fri, 28 Oct 2005 02:40:20 -0700, tshad wrote:
> (snip)
> Hi Tom,
> Yes, you're right. I forgot that. GROUP BY all columns used in the
> expresion makes for a valid query, but it'll only produce the same
> results if no two sets of column values can ever result in the same
> result of the expression.
>
> This has to do with the "official" way to process a SELECT. Official,
> bacuase that's how ANSI says it should be done. In quotes, because all
> major databases will choose other orders to optimize for speed, as long
> as the results are the same as if the official order had been used.
> Step 1: Process FROM clause (includes all JOIN clauses). Results in a
> temporary table (stored internally) that holds all columns of all tables
> used in the FROM clause, with all rows that satisfy the JOIN conditions.
> If old-style FROM cluase is used (i.e. FROM table1, table2, ...), this
> will hold the Carthesian product of the tables.
> Step 2: Process WHERE clause. Check WHERE clause for each row in temp
> table from step 1, and remove row if WHERE clause evaluates to FALSE or
> UNKNOWN.
> Step 3: Process GROUP BY clause. Using table from step 2, form groups of
> rows that share the same value for all columns (or expressions) in the
> GROUP BY clause.
> Step 4: Process HAVING clause. Check HAVING clause for each *group of
> rows* in the temp table after step 3, and remove *complete group* if
> HAVING clause evaluates to FALSE or UNKNOWN.
> Step 5: Process SELECT clause. Result of this step will be a table with
> one column for each entry in the SELECT clause. If no GROUP BY is
> present, than result set will have one row for each row left in the temp
> table. If a GROUP BY is present, than result set will have one row for
> each *group* of rows left in the temp table, and expression in the
> SELECT list can't refer to columns/expressions not in the GROUP BY list,
> unless enclosed in an aggregate function.
> Since the SELECT is processed last, neither the result of the expression
> nor the column alias given to it is available when the GROUP BY is
> processed.
>
That makes sense.

>
I assume the derived table is the inner select?
> I don't see a message by Peter - had I known that you've already been
> given this advise, I wouldn't have repeated it. Was Peter's reply in
> this thread?
>
No, it was in the next one - which was a similar question. His result was:
SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
SUM(days60) AS [60], SUM(days90) AS [90]
FROM (select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
Days30 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
Days60 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
Days90 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)) AS a
GROUP BY productname
I have no problem seeing another similar answer as it helps to see what is
actually happening to see it from a couple of different angles, even if the
result is the same.

> In situations like this, I use a derived table if I have to repeat a
> complicated expression. If I also might want to use the same expression
> in other queries, I might go for a view. If it's this query only, I
> prefer a derived table. If the expression is fairly simple, I just
> repeat it.
Where it was difficult is trying to figure out that I need to create a
temporary table (derived - I assume) and then do a select on that.
Also, why do you need the "AS Der" (in your example)?
It's not used anywhere. I know that Peter did the same thing with his (AS
a)
Thanks,
Tom
> Those are the rules of thumb. Readability and maintainability of code
> are very important too, of course. And for the final decision, at least
> when it's in code that needs to be fast, you'll have to test ... test
> ... test.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 31 Oct 2005 16:23:35 -0800, tshad wrote:
(snip)
>I assume the derived table is the inner select?
Yes.

>No, it was in the next one - which was a similar question. His result was
:
>
(snip query)
That query doesn't look too efficient. I know you didn't ask about this
one, but is there any reason why you can't rewrite it as:
SELECT ProductName,
SUM(PostingsLeft) AS Balance,
SUM(CASE WHEN Age > 0 AND Age <= 30
THEN PostingsLeft ELSE 0 END), 0) AS [30],
SUM(CASE WHEN Age > 30 AND Age <= 60
THEN PostingsLeft ELSE 0 END), 0) AS [60],
SUM(CASE WHEN Age > 60 AND Age <= 90
THEN PostingsLeft ELSE 0 END), 0) AS [90]
FROM (SELECT ProductName,
DATEDIFF(day, CURRENT_TIMESTAMP, DateExpires) AS Age
FROM PurchasedProducts) AS a
GROUP BY ProductName
(snip)
>Also, why do you need the "AS Der" (in your example)?
>It's not used anywhere. I know that Peter did the same thing with his (AS
>a)
The syntax requires it. Each column used in a query must be addressable
by tablename-or-alias + columname. Though it is permitted to leave out
the tablename (or alias) in the actual references, it must still be
known to the database engine.
Since a derived table has no own table name, it can only be referenced
through an alias. That's why the syntax REQUIRES you to use an alias
after each derived table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)