Select Query Error in SQL

Posted by Raj.Trivedi under Sql Server on 3/1/2013 | Points: 10 | Views : 748 | Status : [Member] [MVP] | Replies : 6
Hello Team,

I have 3 tables in a Database.

CustomerDetails
ProductDetails
ProductBooking

create table CustomerDetails
(
CustomerID int identity(1,1)
CustomerName varchar(50)
)

create table ProductDetails
(
ProductID int identity(1,1),
ProductName varchar(50),
ProductPrice decimal(18,2)
)

create table ProductBooking
(
BookingID int identity(1,1),
ProductID int,
CustomerID int,
ProductPrice decimal(18,2),
Quantity int,
TotalPrice decimal(18,2)
)


i am running a select Query to get maximum amount of Purchase made by customer and the name of the customer name but is giving me error

select max(PB.TotalPrice),CU.CustomerName from ProductBooking PB,CustomerDetails CU where
CU.CustomerID = PB.CustomerID

it is giving me an error saying CU.CustomerName
Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Regards,
Raj.Trivedi

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: Ankitsrist on: 3/1/2013 [Member] Starter | Points: 25

Up
0
Down
hello,
query should something like

select max(PB.TotalPrice),CU.CustomerName from ProductBooking PB inner join  CustomerDetails CU on 

CU.CustomerID = PB.CustomerID GROUP BY CU.CustomerName

u have to mention join...now it will work for sure
thanks

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-Innovator on: 3/2/2013 [Member] Bronze | Points: 25

Up
0
Down
When you use any aggregate function in your statement you need to use a GROUP BY CLAUSE in a Query...


SELECT MAX(PRO.TOTALPRICE),CST.CUSTOMERNAME from PRODUCTBOOKING PRO INNER JOIN  CUSTOMERDETAILS CST ON  


CST.CUSTOMERID = PRO.CUSTOMERID GROUP BY CST.CUSTOMERNAME


Join Hands Change lives
Thanks & Regards
Straight Edge Society

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/2/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Raj Try this Query working good

select max(PB.TotalPrice)as TotalPrice,CU.CustomerName from ProductBooking PB,CustomerDetails CU where
CU.CustomerID = PB.CustomerID group by CU.CustomerName


Dont forgot mark as answer useful for this.



Mark as Answer if its helpful to you

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sahoomanoj27 on: 3/14/2013 [Member] Starter | Points: 25

Up
0
Down
select
max(PB.TotalPrice) AS [MAX PRICE],
CU.CustomerName AS [Customer Name]
from

ProductBooking AS PB WITH (NOLOCK)
JOIN CustomerDetails AS CU WITH (NOLOCK)
ON CU.CustomerID = PB.CustomerID
GROUP BY CU.CustomerName

REGARDS
MANOJ SAHOO

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 3/14/2013 [Member] Bronze | Points: 25

Up
0
Down
select max(PB.TotalPrice),CU.CustomerName from ProductBooking PB,CustomerDetails CU where
CU.CustomerID = PB.CustomerID group by CU.CustomerName

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/11/2013 [Member] Starter | Points: 25

Up
0
Down
select max(PB.TotalPrice),CU.CustomerName from ProductBooking PB INNER JOIN CustomerDetails CU

ON
CU.CustomerID = PB.CustomerID

sriram

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response