2

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

Kinchit Dalwani
  • 398
  • 4
  • 19

1 Answers1

1
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! :)

Community
  • 1
  • 1
Sonia Saxena
  • 95
  • 3
  • 11
  • The underlined attributes are the foreign key and table are joined using primary key only! Moreover, using SUM makes no change in the output. @Sonia – Kinchit Dalwani Apr 11 '15 at 22:32
  • Do each of these tables have primary keys? – Sonia Saxena Apr 12 '15 at 05:27
  • By the way, My previous answers about primary key was wrong. I tested on my tables. Even if tables don't have any primary key, group by clause works. I hope that my answer solves your problem. – Sonia Saxena Apr 12 '15 at 06:01
  • it worked! When I tried to remove any table from group by it shows error of aggregate function but don't know why it started working when price and quantity are removed. @Sonia – Kinchit Dalwani Apr 12 '15 at 06:39
  • As I said in my answer, Read about how group clause works when you mention more than one columns. Check your database entries. You'll get it. :) – Sonia Saxena Apr 12 '15 at 06:41