SQL Problem

[smashclash]

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.

 

[Norie]

Are you writing this straight as a SQL statement or using the QBE?

 

[smashclash]

Are you writing this straight as a SQL statement or using the QBE?

trying to do straight SQL

 

[Norie]

Have you tried using the QBE?

Or is there a particular reason why you need to use SQL?

 

[tonnic]

Hi,

is PID a text-field?

If not then change your query like this:

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

 

[Norie]

If PID is a text field I think the SQL statement should be as follows:

SELECT Line_item.PID, Avg(Line_item.qty) AS AvgOfqty
FROM Line_item
GROUP BY Line_item.PID
HAVING (((Line_item.PID)='2'));

If PID is a number then:

SELECT Line_item.PID, Avg(Line_item.qty) AS AvgOfqty
FROM Line_item
GROUP BY Line_item.PID
HAVING (((Line_item.PID)=2));