SQLTeam.com | Weblogs | Forums

Getting sum from joined table data - MS SQL 2014

My project contains below TABLES and sample data for it ::

CREATE TABLE [DBO].[TBL_Budget] (
BudgetId INT IDENTITY(1, 1) NOT NULL
,BudgetName NVARCHAR(200) NOT NULL
,BudgetType INT NOT NULL
,BudgetAmount INT
,CONSTRAINT PK_Budget PRIMARY KEY (BudgetId)
)

BudgetId	BudgetType		BudgetName			BudgetAmount
1			   101			B1				    5,00,000
2			   201			B2				    10,00,000	


CREATE TABLE [DBO].[TBL_School] ( 
     SchoolId INT IDENTITY(1, 1) NOT NULL  
    ,SchoolName NVARCHAR(200) NOT NULL
    ,CONSTRAINT PK_School PRIMARY KEY (SchoolId)
    )
	
SchoolId		SchoolName
1				  NJ.EBS
2				  LA.EBS

CREATE TABLE [DBO].[TBL_Mapped] ( 
     MappedId INT IDENTITY(1, 1) NOT NULL    
    ,MappedType INT NOT NULL  --- {1st quarter, 2nd quarter, 3rd quarter}
    ,MappedAmount INT  
	,BudgetId INT NOT NULL 
	,SchoolId INT NOT NULL 
    ,CONSTRAINT PK_Mapped PRIMARY KEY (MappedId)
    )	
    MappedId	MappedType		MappedAmount	BudgetId	  SchoolId
    1				1				1,00,000		1			1
    2				1				3,00,000		1			2
    3				2				6,00,000		2			2
    4				2				2,00,000		2			1
    5				2				1,00,000		1			1

I have to make a report such that how much amount from each budget is given to schools in quarterly basis.

Please see the attached Report Format data, I can't provide here because the data does not fit into the window.
How to get this report format data? I have tried with this query but not getting the desired result. Also MS SQL Server does not allow to group by in the columns which are not contained in function. What will be the query to get the desired result?

SELECT
A.BudgetId
,A.BudgetName
,A.BudgetAmount
,B.MappedType
,SUM(B.MappedAmount) [TotalMappedAmount]
FROM [DBO].[TBL_Budget] A
INNER JOIN [DBO].[TBL_Mapped] B ON A.BudgetId = B.BudgetId
GROUP BY
A.BudgetId
,B.MappedType

hi

your create table scripts are giving errors

i corrected and below is my script

please click arrow to the left for drop create data script
drop table [DBO].[TBL_Budget]
go 

drop table [DBO].[TBL_School]
go 

drop table tbl_mapped 
go 



CREATE TABLE [DBO].[TBL_Budget] (
BudgetId INT 
,BudgetType varchar(10) NOT NULL
,BudgetName NVARCHAR(200) NOT NULL
,BudgetAmount INT
,CONSTRAINT PK_Budget PRIMARY KEY (BudgetId)
)
go 



insert into tbl_budget select 1,		   101,			'B1',				    500000

insert into tbl_budget  select 2	, 		   201	,		'B2',				    1000000	

select * from tbl_budget 



CREATE TABLE [DBO].[TBL_School] ( 
     SchoolId INT   
    ,SchoolName NVARCHAR(200) NOT NULL
    ,CONSTRAINT PK_School PRIMARY KEY (SchoolId)
    )
	
insert into tbl_school select 1,'NJ.EBS'
insert into tbl_school select 2,'LA.EBS'

select * from tbl_school 
go 

CREATE TABLE [DBO].[TBL_Mapped] ( 
     MappedId  INT
    ,MappedType INT NOT NULL  --- {1st quarter, 2nd quarter, 3rd quarter}
    ,MappedAmount INT  
	,BudgetId INT NOT NULL 
	,SchoolId INT NOT NULL 
    ,CONSTRAINT PK_Mapped PRIMARY KEY (MappedId)
    )	
	go 
insert into tbl_mapped select      1,				1,				100000	,	1	,		1
insert into tbl_mapped select      2,				1,				300000,		1	,		2
    insert into tbl_mapped select  3,				2,				600000	,	2	,		2
    insert into tbl_mapped select  4,				2,				200000	,	2	,		1
    insert into tbl_mapped select  5,				2,				100000	,	1	,		1
	go 
select * from tbl_mapped 
go

Your comments
++++++++++++
I have to make a report such that how much amount from each budget is given to schools in quarterly basis.
+++++++++++

i get
what columns are budget related
what columns are school related
but what columns are quarterly related ??? i mean which columns are quarters ???

all this information needs to be clear to the people trying to help you ..

Doing the SQL looks very very simple !!!
Understandiing what you want is one part .. Doing the SQL is the other

Help us help you
Make it easy and straight forward for us to understand !!!