Group By with Non-Aggregate Function
-
Hello,
I want to write one query which has all fields except one field (Numeric) without aggregate function. When I try to do same SQL gives error message that in You must Group By all fields in select list with Aggregate fuction.
Basic Problem is I want to display one field (Non-Aggregated function) by which I dont wont it to be present in GROUP By clause.
Please Help me ASAP.
--Deepak
-
From my understanding, every selected field must be part of either the GROUP BY clause or an aggregate function. Can you provide some sample data to explain which field is causing a problem for you?
-
All fields in Group By must be aggregated. If you have a field whose records all have only one value, just use max(fieldname) and the largest value will appear -- in this case the only value.
Is this for programming or just an ad hoc TSQL Query? If you are doing ad hoc queries and only want one item summed, perhaps, you can just run 2 different queries, first selecting only that field's aggregates, and then another which shows all detail and no aggregates.
-
Please post the table definition, some sample data and the expected results so we can help you.
-
It sounds like you need to join the detail records to a grouped subquery.
select a.ID, a.f1, a.f2, a.f3, b.f4
from tbl a
inner join (
select ID, SUM(f4) as f4
from tbl group by ID
) b on a.ID = b.ID
-
You cannot perform an aggregation without a group by function.
Please post some sample data and an example of what you want the result to look like.
http://www.aspfaq.com/etiquette.asp?id=5006
The opposite is performing a group by without an aggregate function appears to have a select distinct result.
Use pubs
select ord_date, Stor_ID
from Sales
-- (21 Rows Returned)
select ord_date, Stor_ID
from Sales
group by ord_date, Stor_ID
-- (14 Rows Returned)
-
Are you trying to make people guess what you want?
Can you be a bit more explicit with examples and data?
select distrinct fld1, fld2
from tablex
-- or
select fld1,fld2
from tablex
group by fld1, fl2
are the only options to not use an agregate and remove dups
* Noel -
... still not guessing on those questions
. -
Maybe I have too much time on my hands today, but I'm still guessing.
My previous post was too simplistic, it would produce the same result as putting all non-aggregates fields in the GROUP BY. My idea was that there might be a case where the WHERE clause for the detail records and the aggregate are different. For example, the subquery might sum the amounts for all orders by customer while the main query is selecting order details for specific date ranges.
But if the problem is you are using some non-aggregate function (or other computation) to create a field and don't know how to use that with GROUP BY, there are two ways:
select a, b, sum(c), count(d), <formula> as ComputedField
....
group by a, b, <formula>
or use a subquery just for clarity
select a, b, sum(c), count(d), ComputedField
from (
select a, b, c, d, <formula> as ComputedField
) x
group by a, b, ComputedField
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply