Select, Sum, convert...varchar to float?

Goran Bibic

8y1.2kSQL

Try...

Msg 1035, Level 15, State 10, Line 13
Incorrect syntax near 'cast', expected 'AS'.

select [iznos_sa_pdv], sum(cast(Try_convert(float,[iznos_sa_pdv]))) AS ukupno
from mp_kasa_lista
WHERE [datum] >= CONVERT(datetime, convert(varchar(10), GETDATE() ,120), 120)
AND datum < DATEADD(day, 1, convert(datetime, convert(varchar(10), getdate(), 120), 120))
group by iznos_sa_pdv

Some help?

I need sum of column iznos_sa_pdv type varchar 255...where condition is ok

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted answer
can you please just run a simple SELECT statement with the fields that you want to group by

do not group by i just want to see the data first
SELECT  [iznos_sa_pdv]
FROM    [mp_kasa_lista]
WHERE   [datum] >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
    AND [datum] <  DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE()))
Goran Bibic8y ago
My solution
Datum is date on english
select datum, SUM (Try_Convert(float,[iznos_sa_pdv])) as UKUPNO
from mp_kasa_lista
group by datum
Have just one problem..if value have 47,256.22
Just jump that value....

65.66
or
656.55

Sum working fine

Solution for thousand separator , ?
Goran Bibic8y ago
Shahriar Nikkhah

Result is empty columns
Goran Bibic8y ago
Manas...first result is emtpy, second result is null
Shahriar Nikkhah8y ago
Hi
I think Mamans 2nd option is better and might work (test it please) but does if provide what you want? again test it please

my approch
make things simple and read able if this is a viewyou can make some minor changes
check the WHERE clause
SELECT  [iznos_sa_pdv]
    ,   SUM(TRY_CONVERT(float,[iznos_sa_pdv])) AS ukupno
FROM    [mp_kasa_lista]
WHERE   [datum] >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
    AND [datum] <  DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE()))
GROUP BY [iznos_sa_pdv]


--- FYI
--SELECT    CONVERT(datetime, convert(varchar(10), GETDATE() ,120), 120)
--  ,   DATEADD(day, 1, convert(datetime, convert(varchar(10), getdate(), 120), 120))
--  ,   CONVERT(date, getdate())
--  ,   DATEADD(day, 1, CONVERT(date, getdate()))
--  ,   DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
--  ,   DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE()))
Manas Tripathi8y ago
Solution 1 : (If you still want to group by iznos_sa_pdv)

select [iznos_sa_pdv], sum(Try_convert(float,[iznos_sa_pdv])) AS ukupno
from mp_kasa_lista
WHERE [datum] >= CONVERT(datetime, convert(varchar(10), GETDATE() ,120), 120)
AND datum < DATEADD(day, 1, convert(datetime, convert(varchar(10), getdate(), 120), 120))
group by iznos_sa_pdv

Solution 2 : (Sum of iznos_sa_pdv)

select sum(Try_convert(float,[iznos_sa_pdv])) AS ukupno
from mp_kasa_lista
WHERE [datum] >= CONVERT(datetime, convert(varchar(10), GETDATE() ,120), 120)
AND datum < DATEADD(day, 1, convert(datetime, convert(varchar(10), getdate(), 120), 120))