CODEWITHSUNDEEP
sqlsql-serveraggregate
Pooja
Pooja

Reputation: 33

Problem with max() and min() function

I created following table

create table Interiors(
no integer,
name varchar,
type varchar,
dateofstock datetime,
price decimal(6,3),
discount numeric(6,2))

i want to solve these two queries.

  1. Display the type, sum of price and average of discount by grouping type as 'baby cot'.
  2. Display the type, maximum and minimum of price by grouping type as 'baby cot'.

I wrote following stmts for each resp.

  1. select type, sum(price), avg(discount) from Interiors group by 'baby cot'.
  2. select type, max(price), min(price) from Interiors group by 'baby cot'.

I got following error same for both like that 'type is not an aggregate fun. or it dont have GROUP BY' clause. What should I do for solving that queries.

Upvotes: 0

Views: 2077

Answers (3)

Tom Morgan
Tom Morgan

Reputation: 2365

Have a read of this: http://msdn.microsoft.com/en-us/library/aa258901%28v=sql.80%29.aspx

It's the MSDN entry for SQL Server Aggregate Functions. There's plenty of example, including what you're trying to achieve.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86798

You need to group by the field name, not the contents. If you wish to only include the data where the type is 'baby cot', you would include that in the WHERE clause. For example...

SELECT
  type,
  SUM(price) AS "sum",
  AVG(price) AS "avg",
  MAX(price) AS "max",
  MIN(price) AS "min"
FROM
  Interiors
WHERE
  type = 'baby cot'
GROUP BY
  type

Upvotes: 1

Cine
Cine

Reputation: 4402

group by 'baby cot'.

You cant group by a name of something, you have to group by a column name. For instance do:

select type, sum(price), avg(discount) from Interiors group by type
or
select type, sum(price), avg(discount) from Interiors where type = 'baby cot' group by type

Upvotes: 1

Related Questions