Group by is not the same as MySQL

26 May 2005 7:06 PM

UGH

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_desc

26 May 2005 6:31 PM

SkyWalker

What 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.

Show quoteHide quote

"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 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_desc
>
>
>
>

26 May 2005 8:17 PM

David Portas

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
--

27 May 2005 12:45 PM

UGH

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.

Show quoteHide quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@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
> --
>
>

27 May 2005 10:21 PM

Hugo Kornelis

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)