Get group wise Column Sum in SQL Server

Last Reply 4 months ago By dharmendr

Posted 4 months ago

Hi

How to get sum of money students by year study

I used below code got this result:

CREATE TABLE [dbo].[cash](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[IdStd] [char](11) NOT NULL,
	[Money] [money] NOT NULL,
	[Year] [char](7) NOT NULL
)

INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'Q3LL8wws2Ag', 3000.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'hiWRgAws2Ag', 5000.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'Q3LL8wws2Ag', 2000.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'Q3LL8wws2Ag', 200.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'daUzXgws2Ag', 500.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'RGYnaAws2Ag', 3000.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'dZILTwws2Ag', 4500.0000, N'2019/20')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'dZILTwws2Ag', 2000.0000, N'2020/21')

CREATE TABLE [dbo].[students](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[IdStd] [varchar](20) NOT NULL,
	[LastName] [nchar](90) NULL,	
	[Year] [char](7) NOT NULL
)

INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'dZILTwws2Ag', N'Давлатов Одинамад', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'daUzXgws2Ag', N'Солиев Фирӯзҷон', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'RGYnaAws2Ag', N'Шарипов Садриддин', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'6LtldAws2Ag', N'Пулодов Рустам', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'hiWRgAws2Ag', N'Насимов Шоҳрух', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'Q3LL8wws2Ag', N'Одинаев Комрон', N'2020/21')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'jBSBOVIt2Ag', N'Алимов Синдбод', N'2018/19')

select st.IdStd, st.lastname, sum(c.money)money, c.year from students st inner join cash c on st.IdStd=c.IdStd group by c.Year, c.money, st.lastname, st.IdStd

IdStd

lastname

money

year

dZILTwws2Ag

Давлатов Одинамад

4500,00

2019/20

Q3LL8wws2Ag

Одинаев Комрон

200,00

2020/21

dZILTwws2Ag

Давлатов Одинамад

2000,00

2020/21

Q3LL8wws2Ag

Одинаев Комрон

2000,00

2020/21

Q3LL8wws2Ag

Одинаев Комрон

3000,00

2020/21

RGYnaAws2Ag

Шарипов Садриддин

3000,00

2020/21

hiWRgAws2Ag

Насимов Шоҳрух

5000,00

2020/21

I want this result:

IdStd

lastname

money

year

dZILTwws2Ag

Давлатов Одинамад

4500,00

2019/20

Q3LL8wws2Ag

Одинаев Комрон

5200,00

2020/21

dZILTwws2Ag

Давлатов Одинамад

2000,00

2020/21

RGYnaAws2Ag

Шарипов Садриддин

3000,00

2020/21

hiWRgAws2Ag

Насимов Шоҳрух

5000,00

2020/21

 

Posted 4 months ago

Hi PRA,

Refer below query.

SQL

CREATE TABLE [dbo].[cash](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [IdStd] [char](11) NOT NULL,
    [Money] [money] NOT NULL,
    [Year] [char](7) NOT NULL
)
 
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'Q3LL8wws2Ag', 3000.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'hiWRgAws2Ag', 5000.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'Q3LL8wws2Ag', 2000.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'Q3LL8wws2Ag', 200.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'daUzXgws2Ag', 500.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'RGYnaAws2Ag', 3000.0000, N'2020/21')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'dZILTwws2Ag', 4500.0000, N'2019/20')
INSERT [dbo].[cash] ([IdStd], [Money], [Year]) VALUES (N'dZILTwws2Ag', 2000.0000, N'2020/21')
 
CREATE TABLE [dbo].[students](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [IdStd] [varchar](20) NOT NULL,
    [LastName] [nchar](90) NULL,   
    [Year] [char](7) NOT NULL
)
 
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'dZILTwws2Ag', N'Давлатов Одинамад', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'daUzXgws2Ag', N'Солиев Фирӯзҷон', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'RGYnaAws2Ag', N'Шарипов Садриддин', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'6LtldAws2Ag', N'Пулодов Рустам', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'hiWRgAws2Ag', N'Насимов Шоҳрух', N'2019/20')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'Q3LL8wws2Ag', N'Одинаев Комрон', N'2020/21')
INSERT [dbo].[students] ([IdStd], [LastName], [Year]) VALUES (N'jBSBOVIt2Ag', N'Алимов Синдбод', N'2018/19')

SELECT st.IdStd, st.lastname, SUM(c.[Money]) 'money', c.[Year]
FROM students st 
INNER JOIN cash c ON st.IdStd = c.IdStd 
GROUP BY c.[Year], st.IdStd, st.lastname