Agregate function error in sqlserver 2008

Posted by Klbaiju under Sql Server on 8/12/2013 | Points: 10 | Views : 1511 | Status : [Member] | Replies : 5
Hi,
I want to calculate the aggregate function and display records from 2 tables.
here is the code
select b.bus_id ,sum(CAST(b.fare as int) ) total,bm.bus_name from Busdetails b,BusMaster bm where datename(MONTH,b.tour_date)='august' and b.bus_id=bm.bus_id group by b.bus_id
but showing below error



Msg 8120, Level 16, State 1, Line 19
Column 'BusMaster.bus_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

how to solve this
Regards

Baiju




Responses

Posted by: Bandi on: 8/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--Include that extra column bm.bus_name in GROUP BY clause so that the error won't come.
select b.bus_id ,sum(CAST(b.fare as int) ) total, bm.bus_name
from Busdetails b,BusMaster bm
where datename(MONTH,b.tour_date)='august'
and b.bus_id=bm.bus_id
group by b.bus_id[b], bm.bus_name [/b]

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/12/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Baiju,

The error is coming because you have not include the bm.bus_name in group by clause.
So In SQL Server, you need to explicitly define all the columns in the GROUP BY clause that are not aggregated which are defined on the SELECT clause

Please change your query as per below:-

SELECT b.bus_id , sum(CAST(b.fare as int) ) total, bm.bus_name
FROM Busdetails b INNER JOIN BusMaster bm
ON b.bus_id = bm.bus_id
WHERE DATENAME(MONTH, b.tour_date) = 'august'
GROUP BY b.bus_id, bm.bus_name

Also use inner join.

Happy coding.

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Mahesh,

INNER JOIN is not the mandatory. performance-wise and result-wise both queries are same...
only the difference is the ANSI-92 standard.....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ssj_Kumar on: 8/12/2013 [Member] Starter | Points: 25

Up
0
Down
Add bm.bus_name in the group which will solve your problem

Regards,
Jayakumar Selvakani

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/12/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Chandu,

I understand you point. I have not said that inner join is mandatory.
I jut have suggested to use inner join. Now a days all developers are using inner join including you also.
It will help him future to change the inner join to left or outer join depending on his requirement and it make him easy.

First of All these two Operations are for Two different purposes , While Cartesian Product provides you a result made by joining each row from one table to each row in another table. while An inner join (sometimes called a simple join ) is a join of two or more tables that returns only those rows that satisfy the join condition.

Happy coding.

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response