Sql - Using aggregate functions (min / max) as part of a select statement
I am trying to get the minimum and maximum prices back on the villa booking engine. I have a lookup table that stores the price per week for each villa.
I use the min and max functions for this within the selection, but I'm having a lot of problems. Can someone explain where I am going wrong? Heres sp
ALTER PROCEDURE spVillaGet
-- Add the parameters for the stored procedure here
@accomodationTypeFK int = null,
@regionFK int = null,
@arrivalDate datetime = null,
@numberOfNights int = null,
@sleeps int = null,
@priceFloor money = null,
@priceCeil money = null
A.S. BEGIN - SET NOCOUNT ON added to prevent additional result sets from interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT tblVillas.name,
tblVillas.introduction,
tblVillas.italian_introduction,
tblVillas.uk_content,
tblVillas.italian_content,
tblVillas.sleeps,
tblVillas.postcode,
tblLkUpRegions.regionName,
tblLkUpAccomodationTypes.accomodationType,
MIN(price) As MinPrice,
MAX(price) As MaxPrice
FROM tblVillas
LEFT JOIN tblLkUpRegions on tblVillas.regionFK = tblLkUpRegions.regionID
LEFT JOIN tblLkUpAccomodationTypes on tblVillas.accomodationTypeFK = tblLkUpAccomodationTypes.accomodationId
LEFT JOIN tblWeeklyPrices on tblWeeklyPrices.villaFK = tblVillas.villaId
WHERE
((@accomodationTypeFK is null OR accomodationTypeFK = @accomodationTypeFK)
AND (@regionFK is null OR regionFK = @regionFK)
AND (@sleeps is null OR sleeps = @sleeps)
AND tblVillas.deleted = 0)
GROUP BY tblVillas.name
source to share
You don't elaborate on what problems you are getting, but this is probably one thing: you need to list all non-aggregate columns in the GROUP BY clause ie:
GROUP BY tblVillas.name,
tblVillas.introduction,
tblVillas.italian_introduction,
tblVillas.uk_content,
tblVillas.italian_content,
tblVillas.sleeps,
tblVillas.postcode,
tblLkUpRegions.regionName,
tblLkUpAccomodationTypes.accomodationType
It can be seen from your subsequent comment that some of your columns are of a datatype that cannot be used in the GROUP BY clause. Try this instead:
SELECT tblVillas.name,
tblVillas.introduction,
tblVillas.italian_introduction,
tblVillas.uk_content,
tblVillas.italian_content,
tblVillas.sleeps,
tblVillas.postcode,
tblLkUpRegions.regionName,
tblLkUpAccomodationTypes.accomodationType,
(SELECT MIN(price) FROM tblWeeklyPrices where tblWeeklyPrices.villaFK = tblVillas.villaId) As MinPrice,
(SELECT MAX(price) FROM tblWeeklyPrices where tblWeeklyPrices.villaFK = tblVillas.villaId) As MaxPrice
FROM tblVillas
LEFT JOIN tblLkUpRegions on tblVillas.regionFK = tblLkUpRegions.regionID
LEFT JOIN tblLkUpAccomodationTypes on tblVillas.accomodationTypeFK = tblLkUpAccomodationTypes.accomodationId
WHERE
((@accomodationTypeFK is null OR accomodationTypeFK = @accomodationTypeFK)
AND (@regionFK is null OR regionFK = @regionFK)
AND (@sleeps is null OR sleeps = @sleeps)
AND tblVillas.deleted = 0)
source to share
thanks for the help
When I Group By and include all columns from select except two functions, I get the following error
Msg 306, Level 16, State 2, Procedure spVillaGet, Line 22
It is not possible to compare or sort text, text, and graphic data types, unless the NULL or LIKE operator is used. Msg 306, Level 16, State 2, Procedure spVillaGet, Line 22 Text, text, and graphics data types cannot be compared or sorted, except when using the NULL or LIKE operator.
source to share