Fetch data using group by

ravininave · January 15, 2024, 9:31pm

I have a table which shows common data for 5 days. Here is a query to create it.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ChartMast](
	[ChartDate] [datetime] NOT NULL,
	[ChartVal] [nvarchar](5) NULL,
	[ChartVal1] [nvarchar](5) NULL,
	[ChartVal2] [nvarchar](5) NULL,
	[ChartVal3] [nvarchar](5) NULL,		
	[fromDt] [nvarchar](20) NULL,
	[toDt] [nvarchar](20) NULL,
 CONSTRAINT [PK_ChartMast] PRIMARY KEY CLUSTERED 
(
	[ChartDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-02 00:00:00.000', '80','477','80','190', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-03 00:00:00.000', '55','780','55','889', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-04 00:00:00.000', '34','410','34','225', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-05 00:00:00.000', '27','128','27','505', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-06 00:00:00.000', '07','280','07','188', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-07 00:00:00.000', '06','145','06','448', '02/01/2023', '07/01/2023')

INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-09 00:00:00.000', '53','117','53','269', '09/01/2023', '14/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-10 00:00:00.000', '77','260','77','466', '09/01/2023', '14/01/2023')

INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-11 00:00:00.000', '24','346','24','680', '09/01/2023', '14/01/2023')

INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-12 00:00:00.000', '48','590','48','115', '09/01/2023', '14/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-13 00:00:00.000', '70','124','70','244', '09/01/2023', '14/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-14 00:00:00.000', '85','440','85','853', '09/01/2023', '14/01/2023')

So, I have to group data on fromDt and toDt column.
Like I need all data of fromDt and toDt in one row only so it will show 6 columns in single row
likewise it will show only 2 rows

How could I do it?

JeffModen · January 15, 2024, 10:46pm

This very first thing I'd do is redesign the table. I'll never understand quite why people store numeric values (the value columns) as NVARCHAR(anything) nor why they store perfectly good start and end dates and NVARCHAR((anything). It's a complete waste of clock cycles to put it into such a form, makes proper sorting incredibly aggravating, and takes further clock cycles to change it all back to the correct datatype to do calculations with and then still reformat for display purposes.

I'm not real sure what you mean by that... do you really mean you want the totals for each of the 4 value columns to show up on each row by the fromDt/oDT range?

To do strictly the 6 column by 2 row thing, you could use the following code (this uses the old but very fast and very useful CROSSTAB Method) used...

 SELECT  fromDt
        ,toDt
        ,ChartVal  = SUM(CONVERT(INT,ChartVal ))
        ,ChartVal1 = SUM(CONVERT(INT,ChartVal1))
        ,ChartVal2 = SUM(CONVERT(INT,ChartVal2))
        ,ChartVal3 = SUM(CONVERT(INT,ChartVal3))
   FROM dbo.ChartMast
  GROUP BY fromDt, toDt
  ORDER BY CONVERT(DATE,fromDt,103) --Convert from dd/mm/yyyy format.
         , CONVERT(DATE,toDt  ,103) --Convert from dd/mm/yyyy format.
;

... to produce the following result.

We can also do column and row totals. If you're interested in that, please post back.

You can read/learn a lot more about the CROSSTAB method at the following 2 links...

ravininave · January 15, 2024, 11:03pm

Hello JeffModen,
I am using varchar datatype because sometimes I need to store values like '05'

No, I don't want sum of these columns.
I need all rows for the fromDt and toDt filter in horizontal. For example
fromDt, toDt, alldataofday1,alldataofday2, alldataofday3,alldataofday4,alldataofday5
Likewise

Hope you understand

JeffModen · January 15, 2024, 11:06pm

I don't understand what you mean by "alldataofday1" for example. Please provide that example.
Also, we need to know what version of SQL Server you're using.

ravininave · January 15, 2024, 11:12pm

If we group on fromDt and toDt we will get 6 rows each
So I have to show all 6 rows in one row. So it will show like
fromDt,toDt, ChartVal, ChartVal1, ChartVal2, ChartVal3, ChartVal1, ChartVal2, ChartVal3,ChartVal1, ChartVal2, ChartVal3,ChartVal1, ChartVal2, ChartVal3,ChartVal1, ChartVal2, ChartVal3,ChartVal1, ChartVal2, ChartVal3

ravininave · January 15, 2024, 11:41pm

Likewise data of all 6 rows in 1 row

harishgg1 · January 16, 2024, 8:50am

hi

hope this helps

;with cte as 
( 
select ROW_NUMBER() over(partition by fromdt order by chartdate) as rn ,* from ChartMast
) 
select 
  a.fromDt, a.toDt
	,a.ChartDate, a.ChartVal, a.ChartVal1, a.ChartVal2, a.ChartVal3   
	,a.ChartDate, b.ChartVal, b.ChartVal1, b.ChartVal2, b.ChartVal3   
	,a.ChartDate, c.ChartVal, c.ChartVal1, c.ChartVal2, c.ChartVal3   
	,a.ChartDate, d.ChartVal, d.ChartVal1, d.ChartVal2, d.ChartVal3   
	,a.ChartDate, f.ChartVal, f.ChartVal1, f.ChartVal2, f.ChartVal3   
from cte a 
	join cte b on a.fromDt = b.fromDt and a.rn =1 and b.rn=2
	join cte c on a.fromDt = c.fromDt and a.rn =1 and c.rn=3
	join cte d on a.fromDt = d.fromDt and a.rn =1 and d.rn=4
	join cte e on a.fromDt = e.fromDt and a.rn =1 and e.rn=5
	join cte f on a.fromDt = f.fromDt and a.rn =1 and f.rn=6

ravininave · January 16, 2024, 7:30pm

Hey harishgg1,
Thank you very much. This is what I was needed. I appreciate your help.

ravininave · January 16, 2024, 7:42pm

One more thing I need is what if there is not complete data like in the last week I have a data of only 2 days so remaining 4entries should be blank.
I tried with LEFT JOIN but it's not working. Kindly suggest me

harishgg1 · January 17, 2024, 7:33am

"please"

provide a data sample with this scenario

ravininave · January 17, 2024, 9:33am

INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-02 00:00:00.000', '80','477','80','190', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-03 00:00:00.000', '55','780','55','889', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-04 00:00:00.000', '34','410','34','225', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-05 00:00:00.000', '27','128','27','505', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-06 00:00:00.000', '07','280','07','188', '02/01/2023', '07/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-07 00:00:00.000', '06','145','06','448', '02/01/2023', '07/01/2023')

INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-09 00:00:00.000', '53','117','53','269', '09/01/2023', '14/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-10 00:00:00.000', '77','260','77','466', '09/01/2023', '14/01/2023')

INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-11 00:00:00.000', '24','346','24','680', '09/01/2023', '14/01/2023')

INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-12 00:00:00.000', '48','590','48','115', '09/01/2023', '14/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-13 00:00:00.000', '70','124','70','244', '09/01/2023', '14/01/2023')
INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-14 00:00:00.000', '85','440','85','853', '09/01/2023', '14/01/2023')

INSERT INTO ChartMast (ChartDate,ChartVal, ChartVal1, ChartVal2, ChartVal3,fromDt, toDt) Values ('2023-01-16 00:00:00.000', '12','145','12','352', '16/01/2023', '21/01/2023')

Actually we are showing data of every week. On each day, we enter week start and end date in fromdt,toDt field. So first we get the data on Monday so it should show 1 entry on Tuesday 2 entries likewise

So on last entry of the data given it should show only 1 entry and rest blank entries on next period i.e. '16/01/2023', '21/01/2023'

ravininave · January 18, 2024, 10:42am

@harishgg1 did you find any solution

ravininave · January 18, 2024, 11:03pm

Finally I found the solution @harishgg1 thank you for your help I added a little modification and it solved my problem

LEFT JOIN cte b on a.fromDate = b.fromDate and a.rn =1 and b.rn=2
	LEFT JOIN cte c on a.fromDate = c.fromDate and a.rn =1 and c.rn=3
	LEFT JOIN cte d on a.fromDate = d.fromDate and a.rn =1 and d.rn=4
	LEFT JOIN cte e on a.fromDate = e.fromDate and a.rn =1 and e.rn=5
	LEFT JOIN cte f on a.fromDate = f.fromDate and a.rn =1 and f.rn=6
	WHERE a.rn = 1;

harishgg1 · January 20, 2024, 9:00am

hi ravi

sorry i got busy

glad you found a solution