Member Avatar for denny42

hello all

in table1 I have

Date Value
2/1/2010 100
2/2/2010 110
2/5/2010 95
3/4/2010 150
3/15/2010 220
etc


how to sum the value according to it's month ?
so I got Pebruary' sum value=315
March sum value =370

tq

  • 3 Contributors
  • 3 Replies
  • 96 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by milenio

Dani AI

Generated

wanted monthly totals. and started with string tricks; those can work but are brittle and slow. A reliable approach is to treat the date column as a true date and group by year+month so months from different years do not merge.

Use YEAR()/MONTH() when the column is a date/datetime:

SELECT YEAR([Date]) AS [Year],
       MONTH([Date]) AS [Month],
       SUM([Value]) AS TotalValue
FROM table1
GROUP BY YEAR([Date]), MONTH([Date])
ORDER BY [Year], [Month];

If dates are stored as text, convert them once (prefer TRY_CONVERT / TRY_CAST on modern servers) and group by a stable year-month string to format output:

SELECT CONVERT(CHAR(7), TRY_CONVERT(date, [DateString], 101), 120) AS YearMonth,
       SUM(TRY_CAST([Value] AS DECIMAL(18,2))) AS Total
FROM table1
WHERE TRY_CONVERT(date, [DateString], 101) IS NOT NULL
GROUP BY CONVERT(CHAR(7), TRY_CONVERT(date, [DateString], 101), 120)
ORDER BY YearMonth;

Notes and cautions: ensure the Value column is numeric (use TRY_CAST and cleanse bad rows before summing). On older SQL Server versions without TRY_CONVERT, use ISDATE() plus CONVERT() carefully. For large tables create a persisted computed column of the parsed date (and index it) to avoid converting every row at runtime.

Recommended Answers

All 3 Replies

Member Avatar for freshfitz

I don't think you can sum a table that isn't int. Can you put the values in 2 different tables date in 1 amount in another?

Member Avatar for freshfitz

This worked for me dateof is the table name in database test

select SUM(CONVERT(INT, right(dateof,3))) as total  from test
where dateof < '1/7/2009'
Member Avatar for milenio

I would try with this:
Select
left(cast(Date as nvarchar),1) as Month
sum(Value) as Value

Group By left(cast(Date as nvarchar),1)

Edited by milenio because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.