Stored Procedure Giving Error

Posted by Raj.Trivedi under Sql Server on 3/17/2014 | Points: 10 | Views : 1650 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: A2H on: 3/17/2014 [Member] [MVP] Silver | Points: 25

0	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

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

0	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

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

Latest Posts