Iqra Iqra - 6 months ago 10
MySQL Question

Sql query for joining data of two table with SUM and order by clause

I have two tables in my database.

tbl_Transaction_Detail
and
tbl_Categrory_Type
.
tbl_Transaction_Detail
uses
Category_Type_ID
of
tbl_Category_Type
as foreign key. here is my tables:

tbl_Category_Type

tbl_Transaction_Detail

i want to SUM the
Quantity
of
tbl_Transaction_Detail
for the
Category_Type_ID
(say 31) .

here is my query for this (in sql server 2008)

select tbl_Category_type.Category_type_Name,tbl_Category_type.Description,tbl_Category_type.Image_url,tbl_Category_type.Price,tbl_Category_type.Weight,

Sum(tbl_Transaction_Detail.Quantity)'Quantity' from tbl_Transaction_Detail inner join tbl_Category_type on

tbl_Transaction_Detail.Category_Type_ID=tbl_Category_type.Category_type_ID where tbl_Transaction_Detail.Category_Type_ID=31 order by

tbl_Category_type.Category_type_Name,tbl_Category_type.Description,tbl_Category_type.Image_url,tbl_Category_type.Price,tbl_Category_type.Weight


but it shows error for all column selection one by one.

Answer

Just missing a group by for SQL server.... and use table aliases for readability and spacing!

select CT.Category_type_Name
      ,CT.Description
      ,CT.Image_url
      ,CT.Price
      ,CT.Weight
      ,Sum(TD.Quantity) as Quantity
FROM tbl_Transaction_Detail TD
INNER JOIN tbl_Category_type CT
  on TD.Category_Type_ID=CT.Category_type_ID 
WHERE TD.Category_Type_ID=31 
GROUP BY CT.Category_type_Name
        ,CT.Description
        ,CT.Image_url
        ,CT.Price
        ,CT.Weight
ORDER BY CT.Category_type_Name
        ,CT.Description
        ,CT.Image_url
        ,CT.Price
        ,CT.Weight
Comments