Grouping the data in sql server 2008

Question

I am getting the following output

Date       | MenuID|    MenuName|     MenuCost  |       Qty     |         Total
07-12-2014 | 2     | Special Tea|     20.00     |      2.00     |        40.0000
07-12-2014 | 2     | Special Tea|     20.00     |      3.00     |        60.0000
07-12-2014 | 6     | Cold Coffee |    40.00     |       2.00    |        80.0000

by executing the following query

select  convert(varchar,b.Date,105) as 'Date',m.MenuID, m.MenuName,m.MenuCost, sum(bm.Qty) as 'Qty' ,
    (m.MenuCost*(sum(bm.Qty))) as 'Total'


    from BillMaster b
  left join BillMenuMapping bm on bm.MapBillId = b.BillId
  left join MenuMaster m on m.MenuID = bm.MapMenuId
  where b.Date=@from_date
  group by b.Date,bm.Qty,m.MenuCost,m.MenuName,m.MenuID

I want the out put to be

Date       | MenuID|    MenuName|     MenuCost  |       Qty     |         Total
07-12-2014 | 2     | Special Tea|     20.00     |       5.00    |         100.0000
07-12-2014 | 6     | Cold Coffee |    40.00     |       2.00    |         80.0000

Where am I going wrong. Please Help.

Following are my table structures

Bill Master

BillId  varchar(30) 
TableNo int Checked
Date    datetime    
id  int 


BillMenuMapping


MapID   int 
MapMenuId   int 
MapBillId   varchar(30) 
Qty decimal(7, 2)


MenuMaster


MenuID  int 
MenuName    varchar(100)    
MenuCost    decimal(7, 2)   
ManuActive  bit 

Is it because I have different bill numbers thats why the records are getting repeated.?

Answer 1

You need to remove bm.Qty from Group by. Try changing your query like this.

SELECT CONVERT(VARCHAR, b.date, 105)    AS 'Date', 
       m.menuid, 
       m.menuname, 
       m.menucost, 
       Sum(bm.qty)                      AS 'Qty', 
       m.menucost * ( Sum(bm.qty) )    AS 'Total' 
FROM   billmaster b 
       LEFT JOIN billmenumapping bm 
              ON bm.mapbillid = b.billid 
       LEFT JOIN menumaster m 
              ON m.menuid = bm.mapmenuid 
WHERE  b.date = @from_date 
GROUP  BY b.date, 
          m.menucost, 
          m.menuname, 
          m.menuid