not getting proper output in Group by Clause

Question

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

Perhaps `sum(tblEventItem.Quantity * tblEventItem.Price) AS Price` and remove group by clause in `tblEventItem.Quantity, tblEventItem.Price` — Abhik Chakraborty, Apr 11 '15 at 22:06

score 1 · Accepted Answer · edited May 23 '17 at 12:22

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

edited May 23 '17 at 12:22

Community

1
1

answered Apr 11 '15 at 22:05

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

not getting proper output in Group by Clause

1 Answers1