Group by is not the same as MySQL

Group by is not the same as MySQL
I am migrating some of the query from mysql to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_descWhat values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:

> Group by is not the same as MySQL
>
> I am migrating some of the query from mysql to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and
I
> would have to load the data into the temp file then get rest of the fields
.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it wou
ld
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>|||Your query isn't legal in Standard SQL. I guess that mysql fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that mysql fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:

>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)