CODEWITHSUNDEEP
sql-serverdatedatetimecasting
Dodgeball
Dodgeball

Reputation: 125

sum 1 column and pick the date on another column

I have a table with 2 columns:

date | points

Data:

2015-01-30 00:00:00.000  |    1.2
2015-01-29 00:00:00.000  |    2
2015-01-30 00:00:00.000  |    5
2015-01-27 00:00:00.000  |    7

I want to sum point column based on date. So if I filter date with 2015-01-30 00:00:00.000 then the result would be like the one below:

2015-01-30 00:00:00.000  |    7.5

The record above is what it should look like.

I have a query but it returns

Error converting data type varchar to float.

My SQL code.

SELECT gpsdate, totkm
FROM (
  SELECT gpsdate,
     cast(cast(cast((SUM(KMRUN)) as float) as int) as nvarchar(50)) as totkm
     ,
     RANK() OVER(PARTITION BY gpsdate 
                 ORDER BY SUM(KMRUN) DESC) as R
 FROM view_tracklist_report
  GROUP BY gpsdate) as InnerQuery
 WHERE InnerQuery.R = 1

Upvotes: 0

Views: 40

Answers (3)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

Test Data

CREATE TABLE #Test ([date] DATETIME, points FLOAT);

INSERT INTO #Test
    ([date], points)
VALUES
    ('2015-01-30 00:00:00.000', 1.2),
    ('2015-01-29 00:00:00.000', 2),
    ('2015-01-30 00:00:00.000', 5),
    ('2015-01-27 00:00:00.000', 7)

Actual Code: Not sure why it should sum to 7.5 tho? 5+1.2=6.2

SELECT DISTINCT [date], SUM(points) OVER(PARTITION BY [date]) AS TotPoints
FROM #Test

Upvotes: 2

rtruszk
rtruszk

Reputation: 3922

This is not a good choice to store numerical values as varchars. If you do so you will have to convert them to numbers in every query. And this is not optimal.

So you have to convert your column tu FLOAT. Then you can simply call

SUM(KMRUN)

without any casting.

Now your SUM(KMRUN) is concatenating strings and giving result like

1.2257

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Just group by:

Select gpsdate, Sum(CAST(KMRUN AS float)) as KMRUN
From view_tracklist_report
group BY gpsdate

Upvotes: 1

Related Questions