Select sum and count using Join in SQL Server

Last Reply 2 months ago By pandeyism

Posted 2 months ago

hello sir,

i want sum of industry column in this query filter on join on tehsilid

ZONEID  STATENAME   DISTTNAME   TEHSILNAME  STATEID DISTTID TEHSILID    FY      MONTH   IND CUMMIND FKDEALERCODE    INDDATE
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   1       0   410 1002222 2019-01-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   2       0   410 1002222 2019-02-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   3       0   410 1002222 2019-03-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   4       65  65  1002222 2018-04-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   5       54  119 1002222 2018-05-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   6       74  193 1002222 2018-06-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   7       42  235 1002222 2018-07-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   8       42  277 1002222 2018-08-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   9       60  337 1002222 2018-09-01 00:00:00.000
1       Haryana     Bhiwani     Bhiwani     13      70      622     2018-2019   10      73  410 1002222 2018-10-01 00:00:00.000

 

select count(TV.TehsilID) as TotalVillage ,sm.TDEALERCODE, TM.TehsilID,TM.TehsilName,sm.salesunitID,su.SalesUnitName  from TTEHSILMASTER TM  left join
salesunitmapping Sm on sm.TDEALERCODE=tm.TDEALERCODE and tm.TehsilID = sm.TehsilID inner join salesUniMaster SU on su.SalesUnitPKID=sm.salesunitID 
inner join TVILLAGEMASTER TV on tv.TehsilID=tm.TehsilID inner join TTEHSILMASTER TH on th.TehsilID=tm.TehsilID 
where  Sm.SalesUnitID is not null    group by sm.TDEALERCODE, TM.TehsilID,TM.TehsilName,sm.salesunitID,su.SalesUnitName

in this query result is coming but wrong data coming 

select count(TV.TehsilID) as TotalVillage ,sm.TDEALERCODE, TM.TehsilID,TM.TehsilName,sm.salesunitID,su.SalesUnitName  from TTEHSILMASTER TM  left join
salesunitmapping Sm on sm.TDEALERCODE=tm.TDEALERCODE and tm.TehsilID = sm.TehsilID inner join salesUniMaster SU on su.SalesUnitPKID=sm.salesunitID 
inner join TVILLAGEMASTER TV on tv.TehsilID=tm.TehsilID inner join TTEHSILMASTER TH on th.TehsilID=tm.TehsilID 
where  Sm.SalesUnitID is not null    group by sm.TDEALERCODE, TM.TehsilID,TM.TehsilName,sm.salesunitID,su.SalesUnitName

this query is fine in this query i want sum of industry column from above table like 

totalIndus	TotalVillage	TDEALERCODE	TehsilID	TehsilName	salesunitID	SalesUnitName

 

34030	830	10069878	334	Bhiwani	1	SalesUnit1
4424	280	10069878	336	Loharu - B 1	SalesUnit1
60255	1170	10069878	6468	Dadri	1	SalesUnit1

 

Posted 2 months ago

Hi telldurges,

Refer below sample.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

SQL

SELECT COUNT(emp.EmployeeID) EmployeeID,
	   ord.ShipCountry,ord.ShipCity,
	   SUM(ord.Freight) Freight
FROM Orders ord
INNER JOIN Employees emp ON emp.EmployeeID = ord.EmployeeID
GROUP BY ord.ShipCountry,ord.ShipCity