28-08-2019, Saat: 15:20
az önce bir arakdaşın isteğinden yola çıkarak ve referans gösteriğim sitedeki örnekleri biraz değiştirirek
MS SQL Group kullanımına ait bir kaç örnek yapmaya ve paylaşmaya çalıştım
umaraım faydalı olur
MS SQL Group kullanımına ait bir kaç örnek yapmaya ve paylaşmaya çalıştım
umaraım faydalı olur
USE [Test] GO /****** Object: Table [dbo].[TableOrnek] Script Date: 28.08.2019 15:04:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TableOrnek]( [IdAuto] [int] IDENTITY(1,1) NOT NULL, [Personel] [nvarchar](50) NULL, [Donem] [nvarchar](50) NULL, [Tutar] [decimal](18, 2) NULL, CONSTRAINT [PK_TableOrnek] PRIMARY KEY CLUSTERED ( [IdAuto] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[TableOrnek] ON GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (1, N'ALİ', N'2019/01', CAST(10.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (2, N'ALİ', N'2019/01', CAST(15.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (3, N'ALİ', N'2019/02', CAST(20.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (4, N'ALİ', N'2019/03', CAST(25.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (5, N'VELİ', N'2019/01', CAST(33.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (6, N'VELİ', N'2019/02', CAST(85.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (7, N'VELİ', N'2019/03', CAST(45.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (8, N'AYŞE', N'2019/01', CAST(99.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (9, N'AYŞE', N'2019/02', CAST(55.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (10, N'AYŞE', N'2019/02', CAST(11.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (11, N'AYŞE', N'2019/02', CAST(22.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (12, N'FATMA', N'2019/01', CAST(55.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (13, N'FATMA', N'2019/02', CAST(75.00 AS Decimal(18, 2))) GO INSERT [dbo].[TableOrnek] ([IdAuto], [Personel], [Donem], [Tutar]) VALUES (14, N'FATMA', N'2019/03', CAST(16.00 AS Decimal(18, 2))) GO SET IDENTITY_INSERT [dbo].[TableOrnek] OFF GO
Select * From TableOrnek
Select IsNull(Personel, 'Personel Toplam') As Personel, IsNull(Donem, 'Donem Toplam') As Donem, Sum(Tutar) As Tutar From TableOrnek Group By Cube(Personel, Donem)
Select IsNull(Personel, 'Personel Toplam') As Personel, IsNull(Donem, 'Donem Toplam') As Donem, Sum(Tutar) As Tutar From TableOrnek Group By Rollup(Personel, Donem)
Select IsNull(Personel, 'Personel Toplam') As Personel, IsNull(Donem, 'Donem Toplam') As Donem, Sum(Tutar) As Tutar From TableOrnek Group By Grouping Sets(Personel, Donem)
Select IsNull(Personel, 'Personel Toplam') As Personel, IsNull(Donem, 'Donem Toplam') As Donem, Sum(Tutar) As Tutar From TableOrnek Group By Grouping Sets(Personel, Rollup(Donem))
Select IsNull(Personel, 'Personel Toplam') As Personel, IsNull(Donem, 'Donem Toplam') As Donem, Sum(Tutar) As Tutar From TableOrnek Group By Grouping Sets(Rollup(Personel, Donem))
Bu dünyada kendine sakladığın bilgi ahirette işine yaramaz.