Stored Procedure Giving Error

Posted by Raj.Trivedi under Sql Server on 3/17/2014 | Points: 10 | Views : 689 | Status : [Member] [MVP] | Replies : 2
create Proc GetBillByBillNo
(
@BillNo int
)
as
begin
select SM.BillNo,SM.Quantity,SM.Price,SM.PriceB4VAT,
SM.PriceAfterVAT,SM.Discount,SM.DiscountDetails,SM.GrandTotal,
SM.CardType,SM.CardNumber,SM.IssuedBy,SM.BillDate,SM.CustomerMobileNo,
CONVERT(VARCHAR(10),SM.BillDate,103) 'InvoiceDate',sum(SM.GrandTotal),CM.CustomerName,CM.EmailAddress,
CM.CustomerAddress,PM.ProductName
from SalesMaster SM, CustomerMaster CM,ProductMaster PM

where
SM.ProductId = PM.ProductId
and
SM.CustomerId = CM.CustomerId
and
BillNo = @BillNo
group by SM.BillNo
end

It is giving me an Error

Column 'SalesMaster.Quantity' 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: A2H on: 3/17/2014 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,
Try to add all columns in Group By like given below

create Proc GetBillByBillNo 
(
@BillNo int
)
as
begin
select SM.BillNo,SM.Quantity,SM.Price,SM.PriceB4VAT,
SM.PriceAfterVAT,SM.Discount,SM.DiscountDetails,SM.GrandTotal,
SM.CardType,SM.CardNumber,SM.IssuedBy,SM.BillDate,SM.CustomerMobileNo,
CONVERT(VARCHAR(10),SM.BillDate,103) 'InvoiceDate',sum(SM.GrandTotal),CM.CustomerName,CM.EmailAddress,
CM.CustomerAddress,PM.ProductName
from SalesMaster SM, CustomerMaster CM,ProductMaster PM

where
SM.ProductId = PM.ProductId
and
SM.CustomerId = CM.CustomerId
and
BillNo = @BillNo
group by
SM.BillNo,
SM.Quantity,
SM.Price,
SM.PriceB4VAT,
SM.PriceAfterVAT,
SM.Discount,
SM.DiscountDetails,
SM.GrandTotal,
SM.CardType,
SM.CardNumber,
SM.IssuedBy,
SM.BillDate,
SM.CustomerMobileNo,
SM.BillDate,
CM.CustomerAddress,
PM.ProductName

end


Thanks,
A2H
My Blog

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

Posted by: Bandi on: 3/17/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Put all column names (except GrandTotal) that are present in SELECT clause into GROUP BY clause....

If that above is not working for u, do as follows:
select SM.BillNo,
SUM(SM.Quantity) Quantity,
SUM(SM.Price) Price,
SUM(SM.PriceB4VAT) PriceB4VAT,
SUM(SM.PriceAfterVAT) PriceAfterVAT,
SUM(SM.Discount) Discount,
MAX(SM.DiscountDetails) DiscountDetails,
SUM(SM.GrandTotal) GrandTotal,
MAX(SM.CardType) CardType,
MAX(SM.CardNumber) CardNumber,
MAX(SM.IssuedBy) IssuedBy,
MAX(SM.CustomerMobileNo) CustomerMobileNo,
CONVERT(VARCHAR(10),MAX(SM.BillDate),103) 'InvoiceDate',
MAX(CM.CustomerName) CustomerName,
MAX(CM.EmailAddress) EmailAddress,
MAX(CM.CustomerAddress) CustomerAddress,
MAX(PM.ProductName) ProductName
from SalesMaster SM, CustomerMaster CM,ProductMaster PM
where
SM.ProductId = PM.ProductId
and
SM.CustomerId = CM.CustomerId
and
BillNo = @BillNo
group by SM.BillNo


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Login to post response