How to get total sum across the table and sum grouped by date ranges

Question

Post reply

How to get total sum across the table and sum grouped by date ranges

SSCNewbee

Right there with Babe

Points: 788

September 14, 2023 at 5:13 pm

#4286522

Here is sample code and what I tried to achieve, but not getting the output, any advice?

select k.charges,
sum(case when k.range>='101' then charges else 0 end) as '101charges',
sum(case when k.range>='201' then charges else 0 end) as '201charge'
from
(select charges,substring(daterange,1,3) as range
from dbo.charges,
)k

Capture914

CREATE TABLE charges (
    charges money not null,
    dayrange nvarchar(14) not null
);
Insert into charges (charges,dayrange)
Values (100,’0-100’);
Values (150,’101-200’);
Values (700,’201=300’);
Values (20,’301-400’)

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

Maybe try this:

select k.charges,
sum(case when k.range>='101' then charges else 0 end) as '101charges',
sum(case when k.range>='201' then charges else 0 end) as '201charge'
from
(select charges,case when substring(daterange,1,3) like '0-%' then '000' --<<--
    else substring(daterange,1,3) end as range
from dbo.charges
)k

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SSCNewbee Right there with Babe Points: 788 More actions · Answer 2

Thanks Guru but that doesn't give me overall total and break down by days range

ScottPletcher SSC Guru Points: 101096 More actions · Answer 3

What result do you want? You never explicitly stated the results you want to see.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kaj SSCommitted Points: 1605 More actions · Answer 4

Sorry, but you've been extremely sloppy with your example code/data.

The insert into statement can't even pass a parse test.
There are different column names used in the table and in the query (dayrange vs daterange).
The example data does nothing to help us help you. It needs to be closer to your actual data (which I guess is a list of dates or something like that)
Etc.

I have tried to change your code so that it at least will run, but I still can't figure out what to do with the emulation data as it stands.

CREATE TABLE #charges (
    charges INT NOT NULL,
    dayrange NVARCHAR(14) NOT NULL
);
INSERT INTO #charges (charges,dayrange)
VALUES 
(100,'0-100'),
(150,'101-200'),
(700,'201-300'),
(20,'301-400');

SELECT 
k.charges,
SUM(CASE WHEN k.range>='101' THEN charges ELSE 0 END) AS '101charges',
SUM(CASE WHEN k.range>='201' THEN charges ELSE 0 END) AS '201charge'
FROM (
SELECT charges,SUBSTRING(dayrange,1,3) AS RANGE
FROM #charges
)k
GROUP BY k.charges

Maybe others can where I fail... 🙂

drew.allen SSC Guru Points: 76985 More actions · Answer 5

I've updated the code to a) use a temp table instead of a permanent table. Here is the setup code:

DROP TABLE IF EXISTS #charges;

CREATE TABLE #charges (
    charges money not null,
    dayrange nvarchar(14) not null
);
Insert into #charges (charges,dayrange)
Values (100,'0-100')
     , (150,'101-200')
     , (700,'201=300')
     , (20,'301-400')

Note that the values are all part of the same statement, so they are separated by commas, not semicolons and the VALUES keyword is only specified once at the beginning instead of for every value.

This code gets close to your screenshot.

SELECT *
     , SUM(charges) OVER() AS charges
     , '>=' + LEFT(LEAD(dayrange) OVER(ORDER BY dayrange), 3) AS daterange
     , SUM(charges) OVER(ORDER BY dayrange ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS charges1
FROM #charges AS k
ORDER BY dayrange;

NOTE: It's unclear why your third row doesn't have any values for the last three columns. I also left out the blank column.

NOTE 2: I specifically used + instead of CONCAT(), because I wanted it to create a NULL value for the last row.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 6

Firstly please do not take my constructive criticism poorly, as it is meant to help you improve future questions so that you can get quicker answers as well as perhaps help better understand your own question allowing you to perhaps solve it before you even post it.

Now as has been previously mentioned and you had to clarify -- what you were trying to achieve was not initially clearly stated. Which always makes it much more difficult for us to help you. So it is always best to make sure you fully and completely state all the parameters and all your expected results clearly and concisely.

Next your script was choke full of bugs and would never have worked as it stands, as others also pointed out. Supplying broken code examples is next to worthless and only greatly delays or prevents you from getting the answer you seek.

Still, looking at your initial question and guessing that it was just a half-hearted attempt on your part -- I produced the following adjustments.

Note I also went under the assumption that the source table you provided, you are not allowed to change (which you also did not state) as frankly that table is a horribly structured. So I hope the following helps.

CREATE TABLE [dbo].[tbCharges]
    ( [Charges]  MONEY       NOT NULL
     ,[DayRange] VARCHAR(14) NOT NULL
    );
GO

INSERT INTO [dbo].[tbCharges]
    ( [Charges]
     ,[DayRange]
    )
VALUES ( 100, ’0-100’ ) 
      ,( 150, ’101-200’ )
      ,( 700, ’201-300’ )
      ,( 20, ’301-400’ );
GO

CREATE TABLE #WorkTable
    ( [BgnDay]  INT   NOT NULL
     ,[EndDay]  INT   NOT NULL
     ,[Charges] MONEY NOT NULL
    );
GO

-- This is minor unless the table is huge
ALTER TABLE #WorkTable
  ADD CONSTRAINT [PK_BgnDay]
PRIMARY KEY CLUSTERED ( BgnDay );

INSERT INTO #WorkTable
    ( [BgnDay]
     ,[EndDay]
     ,[Charges]
    )
SELECT CAST( SUBSTRING( [DayRange], 0, CHARINDEX ('-', [DayRange] ) - 1) AS INT )
      ,CAST( SUBSTRING( [DayRange], CHARINDEX ('-', [DayRange] ) + 1, 10 ) AS INT )
      ,[Charges]
  FROM [dbo].[tbCharges];
GO
-- Note your SUBSTRING( [DayRange], 1, 3 ) would break on '0-100'

/*
Expected Totals
Date Range    Charges
   >= 0         970
   >= 101       870
   >= 201       720
   >= 301        20
*/SELECT SUM( CASE WHEN [BgnDay] >= 0
                 THEN [Charges]
                 ELSE 0
            END
          ) AS 'AllCharges'
      ,SUM( CASE WHEN [BgnDay] >= 101
                 THEN [Charges]
                 ELSE 0
            END
          ) AS '101+Charges'
      ,SUM( CASE WHEN [BgnDay] >= 201
                 THEN [Charges]
                 ELSE 0
            END
          ) AS '201+Charges'
      ,SUM( CASE WHEN [BgnDay] >= 301
                 THEN [Charges]
                 ELSE 0
            END
          ) AS '301+Charges'
      ,[rslt].[Charges]
  FROM #WorkTable;
GO

/*
Query Results

AllCharges   101+Charges   201+Charges   301+Charges
   970           870           720           20
*/

This reply was modified 1 year, 1 month ago by Dennis Jensen.
This reply was modified 1 year, 1 month ago by Dennis Jensen.

SSCNewbee Right there with Babe Points: 788 More actions · Answer 7

Thanks everyone. Dennis no offense taken. I agree my code was sloppy.