CODEWITHSUNDEEP
mysqlgroup-byinner-join
Kinchit Dalwani
Kinchit Dalwani

Reputation: 398

not getting proper output in Group by Clause

I have been trying the following query using group by and inner join clause in asp.net:

SELECT tblVendorItem.Name AS Product, 
       tblEventItem.Quantity * tblEventItem.Price AS Sale
FROM 
       tblEventService 
INNER JOIN tblEventItem ON 
       tblEventService.EventServiceID = tblEventItem.EventServiceID 
INNER JOIN tblVendorItem ON 
       tblVendorItem.VendorItemID = tblEventItem.VendorItemID
WHERE        
       (tblEventService.VendorID = 2)
GROUP BY 
        tblVendorItem.Name, tblEventItem.Quantity, tblEventItem.Price

On executing this, what I get is:

enter image description here

What I really want is, Product should be not repeated and the total sales should come!

For eg: Mercedes 75

Can any one help me out?

I am attaching the database also:

enter image description here

Upvotes: 2

Views: 40

Answers (1)

Sonia Saxena
Sonia Saxena

Reputation: 95

INNER JOIN tblEventItem ON 
   tblEventService.EventServiceID = tblEventItem.EventServiceID 

GROUP BY 
    tblVendorItem.Name, tblEventItem.Quantity, tblEventItem.Price

In the group by clause, remove tblEventItem.Quantity, tblEventItem.Price. Hence your query should be changed in two places. First, SUM(price*quantity) and second:

GROUP BY 
    tblVendorItem.Name

The reason is this: Group By X means put all those with the same value for X in the one group.

Group By X, Y means put all those with the same values for both X and Y in the one group.

Group By X means put all those with the same value for X in the one group.

Group By X, Y, Z means put all those with the same values for both X ,Y and Z in the one group. I hope this helps.Using group by on multiple columns Kudos! :)

Upvotes: 1

Related Questions