Group By

andrekl · November 9th, 2003, 04:30 AM

I notice that in Mircosoft Access there is a keyword to use if i want to get my required result. i have to use 'First' keyword.

But in SQl query analyser (transact sql), theres not such keyword. They prompt out 'FIRST is not a recognised funtion name'.

Jeff Mason · November 9th, 2003, 07:58 AM

Quote:

quote:Originally posted by andrekl
I notice that in Mircosoft Access there is a keyword to use if i want to get my required result. i have to use 'First' keyword.

But in SQl query analyser (transact sql), theres not such keyword. They prompt out 'FIRST is not a recognised funtion name'.

There is no such function as 'FIRST' in SQL Server. SQL Server, unlike Access, is a real relational database system, and as I have said repeatedly, the concept of "first" has no meaning in a relational database.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff Mason · November 9th, 2003, 08:11 AM

Quote:

quote:Originally posted by andrekl
im using 'group by' to group the records with the same ID into one single record.

when i use group by, sql needs an aggregate function right?

If you want to 'group' the rows for a single ID into a single result, then by definition you want to aggregate the values from each row with the same ID into that single resultant row. That's what a grouping operation means: you combine the values in some way into a single result.

The way you combine those values is given by the various aggregate functions, like MIN, MAX, SUM, COUNT etc.

SQL requires that each column in a resultset for which grouping is done be either the results of an aggregate function, or be one of the columns that you are grouping by. That's why you get an error message if you don't do that.

Quote:

quote:
so, im using the max() function. In which they will get the maximum value of the field with the same ID. Which i dont want.

I want the record value from the same row instead of jumbled data from mix records with same ID.

Anyway other method to go with?

Then don't use GROUP BY.

I don't think we are communicating very well here. Did you try the suggestions I made in a prior post? The query:

Code:

SELECT ID, NAME, STATE, COUNTRY, CODE
    FROM buyer B1 
WHERE CODE=(SELECT MAX(CODE) FROM buyer B2 WHERE B1.ID=B2.ID)

will select the values from the row with the maximum value of CODE for each ID. Use the MIN function instead to select the row with the smallest value of CODE for each ID.

You cannot select the 'first' row for each ID, as the concept of 'first' is meaningless in a relational database. But, by using the CODE column to order the values of each row for a given ID, we can select the smallest (or largest) value of the ordering column CODE, and that's what the query above will do. It does not use the GROUP BY operation, because that operation does not apply here, if I understand your requirement correctly.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

andrekl · November 9th, 2003, 01:20 PM

I got your point Jeff. The code u given me solved partly my problem.

What if i want to sum the quantity if same ID was found? Then i have to use group by. Which i will group by ID and sum it up.

SELECT ID, NAME, STATE, COUNTRY, CODE, sum(QUANTITY)
FROM buyer B1 --'group by ID
WHERE CODE=(SELECT MAX(CODE) FROM buyer B2 WHERE B1.ID=B2.ID)

As i have added some coding inside, which will still not perfect,
what are the modifications i need to add in to archive my requirement?

Jeff Mason · November 9th, 2003, 10:16 PM

I don't understand.

If you want to compute the SUM of QUANTITY by ID, then what relationship do the unique values of NAME, STATE, COUNTRY, and CODE for each ID have to that sum? In other words, why would you want to compute the sum across the group but also associate values such as NAME which can be different for the same value of ID to that group-wide sum? That association makes no sense to me, I'm afraid.

Let's step back a moment, and why don't you explain what problem you are trying to solve, rather than presenting solutions which don't work?

BTW, the following query will compute the SUM across the group, but still return only the values associated with the maximum CODE for each ID (even though I don't think this makes much sense):

Code:

SELECT ID, NAME, STATE, COUNTRY, CODE, 
    (SELECT SUM(QUANTITY) FROM buyer B0 WHERE B1.ID=B0.ID) as TotalQuatity
    FROM buyer B1
WHERE CODE=(SELECT MAX(CODE) FROM buyer B2 WHERE B1.ID=B2.ID)

This is just a variation on the subquery theme...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

andrekl · November 10th, 2003, 11:04 AM

Let me explain my requirements:-

I have buyers who might have registered their account in different COUNTRY.

Everytime they registered, their ID and NAME will be the same. But STATE and COUNTRY will be different.

Now i want to display all buyers. If ID is the same, then i will sum up the quantity. Then i will send out a letter to the buyer. The address of the buyer which include STATE and COUNTRY must be from either one of the record if ID is the same.

My problem is i get jumbled record if i used group by with aggregate function if there is same ID in the records. (Refer to my previous posting for sample data)

Hope u understand me now..

Jeff Mason · November 10th, 2003, 11:33 AM

So why won't:

Code:

SELECT ID, NAME, STATE, COUNTRY, CODE, 
    (SELECT SUM(QUANTITY) FROM buyer B0 WHERE B1.ID=B0.ID) as TotalQuatity
    FROM buyer B1
WHERE CODE=(SELECT MAX(CODE) FROM buyer B2 WHERE B1.ID=B2.ID)

do the job? Substitute MIN for MAX in the second subquery if you want the NAME, STATE, COUNTRY, and CODE values associated with the lowest value of CODE for a given ID instead of the highest value.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com