Saturday, February 25, 2012

perform aggregate function & group by

Hello, anyone can help?
below SQL works in sybase but fail in sql2000. sql2000 show error on the sum
of netweight (cannot perform aggregate function) and group by commodity_code
(invalid column name !!)
SELECT
(select HTS
from bur_inv_item_list
where item = material
and plantcode = plant) as commodity_code,
(select DESCRIPTION
from bur_inv_item_list
where item = material
and plantcode = plant) as description,
uom as uom,
(select Orig
from bur_inv_item_list
where item = material
and plantcode = plant) as coo,
netweight =
sum(case when plant = '0014' or plant = '0024' then
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant), 2)
else
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant) /2.20462, 2)
end),
sum(ext_cost) as cost
from bur_inv_cntr_list
group by
commodity_code,
description,
uom,
coo
order by
commodity_code,
description,
uom,
coo
;The logical evaluation order of a SELECT statement is (top to bottom):
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP
As you see, the SELECT hasn't happened yet when the GROUP BY is performed. T
his mean that you cannot
refer to any column alias name in the GROUP BY clause. Some product diverts
from the ANSI SQL
standard behavior, SQL Server does not (in this regard). So push the express
ions in a derived table
and work against that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Hello, anyone can help?
>
> below SQL works in sybase but fail in sql2000. sql2000 show error on the s
um of netweight (cannot
> perform aggregate function) and group by commodity_code (invalid column na
me !!)
>
> SELECT
> (select HTS
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as commodity_code,
> (select DESCRIPTION
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as description,
> uom as uom,
> (select Orig
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as coo,
> netweight =
> sum(case when plant = '0014' or plant = '0024' then
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant), 2)
> else
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant) /2.20462, 2)
> end),
> sum(ext_cost) as cost
> from bur_inv_cntr_list
> group by
> commodity_code,
> description,
> uom,
> coo
> order by
> commodity_code,
> description,
> uom,
> coo
> ;
>|||hi tibor,
thanks for your info. do you have any sample/case. I want to see the sample
to modify below script.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
> The logical evaluation order of a SELECT statement is (top to bottom):
> FROM
> WHERE
> GROUP BY
> HAVING
> SELECT
> ORDER BY
> TOP
> As you see, the SELECT hasn't happened yet when the GROUP BY is performed.
> This mean that you cannot refer to any column alias name in the GROUP BY
> clause. Some product diverts from the ANSI SQL standard behavior, SQL
> Server does not (in this regard). So push the expressions in a derived
> table and work against that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Here's a very simple example where a derived table is used so you don't have
to repeat the DATEPART
expression:
USE pubs
SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
FROM titles
GROUP BY DATEPART(mm, pubdate)
SELECT pub_month, COUNT(*) AS titles
FROM
(
SELECT DATEPART(mm, pubdate) AS pub_month
FROM titles
) AS i
GROUP BY pub_month
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> hi tibor,
> thanks for your info. do you have any sample/case. I want to see the sampl
e to modify below
> script.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Many Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebF1yHSaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Here's a very simple example where a derived table is used so you don't
> have to repeat the DATEPART expression:
> USE pubs
> SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
> FROM titles
> GROUP BY DATEPART(mm, pubdate)
> SELECT pub_month, COUNT(*) AS titles
> FROM
> (
> SELECT DATEPART(mm, pubdate) AS pub_month
> FROM titles
> ) AS i
> GROUP BY pub_month
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>

No comments:

Post a Comment