CODEWITHSUNDEEP
sql
Tess Brookes
Tess Brookes

Reputation: 1

Column <name> is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I am fairly new to SQL and have a simple question regarding the error message I received (Title of this question). I'm trying to find the total value of the items in inventory (itemQuantity * itemCost), the average item cost, and the potential lost revenue (itemQuantity * itemMktValue)

SELECT
    (itemQuantity * itemCost) 'Total',
    AVG (itemCost) 'AvgerageItemCost',
    (itemQuantity * itemMktValue) 'LossedRevenue'
FROM
    Item;

If anyone could help me out it would be much appreciated.

Upvotes: 0

Views: 1716

Answers (3)

PM 77-1
PM 77-1

Reputation: 13334

If I understood you correctly, you need something like this:

SELECT
    SUM((itemQuantity * itemCost) 'Total',
    AVG (itemCost) 'AvgerageItemCost',
    SUM(itemQuantity * itemMktValue) 'LossedRevenue'
FROM
    Item;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Your select statement contains an aggregation function in it (AVG). Because of this, SQL assumes that you have an aggregation query.

There is no group by clause, so SQL is expecting everything to be aggregated, as in:

select sum(itemQuantity), count(*)
from item

However, in your query, you have several variables that are not the arguments to aggregation functions -- itemQuantity for example. Presumably, you want something like:

SELECT sum(itemQuantity * itemCost) 'Total',
       AVG (itemCost) 'AvgerageItemCost',
       sum(itemQuantity * itemMktValue) 'LossedRevenue'
FROM Item;

You might also want this information on a per-item basis, in which case, you would use group by:

SELECT itemid, sum(itemQuantity * itemCost) 'Total',
       AVG (itemCost) 'AvgerageItemCost',
       sum(itemQuantity * itemMktValue) 'LossedRevenue'
FROM Item
group by itemid

Upvotes: 3

user359040
user359040

Reputation:

Try:

SELECT
    SUM(itemQuantity * itemCost) 'Total',
    AVG (itemCost) 'AvgerageItemCost',
    SUM(itemQuantity * itemMktValue) 'LossedRevenue'
FROM
    Item;

Upvotes: 1

Related Questions