Aggregate functions and Group by clause

[Rena]

Hi all,

I am still in the reading stage of SQL programming. There is something that confuses me that I hope somebody on this list can explain. My sources make the following statement:

"Any other columns that appear in the select list must be used as arguments of an aggregate function".

I interpret this to mean that I cannot put columns in the select statement that are not aggregate functions. For example, the following would be invalid:

Select Customer, SaleDate, DateDiff(Day, SaleDate, ShipDate) As DaysToShip, Avg(TotalAmt) as AvgAmt
From Sale
Where Shipdate Is Not Null
Group By SaleDate, DateDiff(Day, SaleDate, ShipDate)

If the above is invalid can somebody explain why there is this limitation in SQL?

TIA!
Rena

 

[bperry]

Hi there,
When we say Group By, we are really saying 'Summarize by'.

ie
Select CustId, SUM(Amount)
From Sales Group by CustId

SQL says: "Summarize all the rows by each CustId, return 1 row for each customer, and add up the Amounts".

Okay,that was easy. Now if was say:

Select CustId, SaleDate
From Sales Group By CustId

SQL Says: "I see you want me to summarize by CustId, and return 1 row for each customer. But for all those rows for each customer, I can't tell WHICH SaleDate you want to see: the first saledate for that customer? the last one? the middle one?" Since SQL doesn't know which one we want, it gives us a syntax error.

To get around the error, we must tell SQL which Saledate we want by using an aggregate function:

Select CustId, MAX(SaleDate)
From Sales Group By CustId

Now SQL knows what to do: for each customer, return the biggest (ie most recent) SaleDate.
------------------
It is a little confusing. Does that help?

bperry

 

[Rena]

The fog is getting a little thinner.

So to use your example, if I want the detail information can I write the following:

Select CustId, SaleDate
From Sales Group By CustId, SaleDate

If not, how does one accomplish it? I am use to Visual FoxPro and Crystal Reports. They both do group by but it doesn't seem to be handled this way.

Thanks,
Rena

 

[bperry]

>>So to use your example, if I want the detail
>>information can I write the following:
>>Select CustId, SaleDate
>> From Sales Group By CustId, SaleDate

Yes, you can absolutely say that, and in some situations you certainly will. But remember, Group By means Summarize By, and so that's just what will happen: one summarized row for every unique cust+saledate

Cust Date
123 04/04/02
123 04/05/02
321 04/04/02
321 04/05/02
321 04/06/02

Visual FP and CR don't have any secret methods or magic bullets: all they do is issue sql statements to SQL Server, and these are statements we can write ourselves when we need to.

Hope the fog is still getting lighter. If you have a specific query or situation that puzzles you, I'm sure someone in this forum can help.

rgrds
bperry