AVG function problem 1

[smashclash]

I'm a noob with SQL so please have some patience with me.

I have a table with two columns. One is PID which is various whole numbers and the other is QTY (Quantity). I'm trying to write an SQL statement that will list the average quantity whenever PID = 2.

Right now I have

SELECT Pid, AVG(qty)
FROM Line_item
Where Pid = '2';


But I keep getting the error - tried to execute a query that does not include the specified expression 'pid' as part of the aggregate function. Anyone have any ideas? TIA.

 

[mrdenny]

Yep.

select pid, avg(qty)
from Line_Item
where Pid = 2
group by pid
go
--Or this will work as well.
select avg(qty)
from Line_Item
where Pid = 2

The reason that the second one works as well is because we know that pid will be 2. We told it that pid must be 2, so there is really no readon to output that column. Now if you wanted a report for all products, then you would want to use this.

select pid, avg(qty) as 'Average Sold', sum(qty) 'Total Sold'
from Line_Item
group by pid

You'll notice that I've also added some formatting in there, as well as another column, to show some other things you can do.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[WhiteKnight57t]

so anything not an aggregate function "AVG" has to be in the Group by

SELECT Pid, AVG(qty)
FROM Line_item
Where Pid = '2'
GROUP BY Pid
;

==========================
Date is a way to show you care
Time is a great healer
DateTime is just damn confusing

Tim

 

[mrdenny]

Correct. Anything that is in the select statement needs to be the group by unless it is an aggregate function. The aggregate functions are SUM, AVG, MAX, MIN, and COUNT. There is more info available under "aggregate functions" in BOL.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

Well, there is at least one exception. You can add a constant column without including it in a Group By clause. Maybe for something like this:

[Blue]SELECT[/Blue] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]Field[Gray])[/Gray] AggAmt[Gray],[/Gray] [red]'Sum'[/red] AggType [Blue]FROM[/Blue] MyTable [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]Field[Gray])[/Gray][Gray],[/Gray] [red]'Max'[/red] [Blue]FROM[/Blue] MyTable [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [Fuchsia]MIN[/Fuchsia][Gray]([/Gray]Field[Gray])[/Gray][Gray],[/Gray] [red]'Min'[/red][Blue] FROM[/Blue] MyTable

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

OK, you got me there. I forgot about that one.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)