• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Error in aggregate function

select  op.style_code, rl.return_code,Sum(dl.col020)

from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op

where dh.date_despatched between '01-11-2007' and '08-11-2007'
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code


when i run it i get

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an argument.

can someone help please
0
ammartahir1978
Asked:
ammartahir1978
  • 17
  • 11
  • 7
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the message is clear, I would think....
the field dl.col020 is of data type varchar, which does not look like "normal"...
can't you change the field's data type?

anyhow, you might want to try this:
select  op.style_code, rl.return_code,Sum(cast(dl.col020 as int)) 
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op 
where dh.date_despatched between '01-11-2007' and '08-11-2007'
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code

Open in new window

0
 
ammartahir1978Author Commented:
what this CAST used for just for my knowledge
0
 
ammartahir1978Author Commented:
i get this error now

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '.13' to a column of data type int.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
ammartahir1978Author Commented:
and when i run this code

select  op.style_code, rl.return_code,dl.col020 as QTY_Despatched, dh.date_despatched

from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op

where dh.date_despatched between '01/11/2007' and '08/11/2007'
and dh.s_order_ref = dl.col003
and dl.col001 = rh.despatch_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code,dl.col020,dh.date_despatched
order by op.style_code


even i have the date field filter but its still giving me data for 1999 and 2000 extra, where i am asking only 1 week data, why is that please help
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Syntax error converting the varchar value '.13' to a column of data type int.

we need decimal data type, then:


select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4))) 
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op 
where dh.date_despatched between '01-11-2007' and '08-11-2007'
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code

Open in new window

0
 
ammartahir1978Author Commented:
can you please see the above comments as well AngelllI
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess that the field dh.date_despatched  is also varchar?
so the comparison is made on varchar, and not on date basis. '01-11-2007' is varchar and NOT date (although the string looks like date).


select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4))) 
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op 
where convert(datetime, dh.date_despatched , 105) >= convert(datetime, '01-11-2007',105) 
and convert(datetime, dh.date_despatched , 105) < dateadd(day, ,1, dateconvert(datetime, '08-11-2007', 105))
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code

Open in new window

0
 
ammartahir1978Author Commented:
this is the error i get

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
0
 
imitchieCommented:
I fixed angel's code
select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4))) 
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op 
where convert(datetime, dh.date_despatched , 105) >= convert(datetime, '01-11-2007',105) 
and convert(datetime, dh.date_despatched , 105) < dateadd(day, 1, dateconvert(datetime, '08-11-2007', 105))
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code

Open in new window

0
 
ammartahir1978Author Commented:
Server: Msg 195, Level 15, State 10, Line 4
'dateconvert' is not a recognized function name.

this is the error now
0
 
imitchieCommented:
On the other hand, can you please run this query so that we know what columns we're dealing with to make sure we gave you the best advice?

select name, xtype, length, * from syscolumns
where id in (object_id('optdespline'), object_id('optdesphdr'))
and name in ( 'col020', 'date_despatched' )

please paste the result here
0
 
imitchieCommented:
select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4)))
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op
where convert(datetime, dh.date_despatched , 105) >= convert(datetime, '01-11-2007',105)
and convert(datetime, dh.date_despatched , 105) < dateadd(day, 1, convert(datetime, '08-11-2007', 105))
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code
0
 
ammartahir1978Author Commented:
this is the result for you query you asked me to run IMITCHIE

date_despatched      167      255      date_despatched      556789291      167      2      167      255      0      0      6      -6      0      0      0      0      0      0      6      NULL      -6      872468488      0      24      39      2      NULL      255      NULL      0      0      1      SQL_Latin1_General_CP1_CI_AS      0x0904D00034
Col020      167      255      Col020      588789405      167      2      167      255      0      0      20      -20      0      0      0      0      0      0      20      NULL      -20      872468488      0      24      39      2      NULL      255      NULL      0      0      1      SQL_Latin1_General_CP1_CI_AS      0x0904D00034
0
 
ammartahir1978Author Commented:
after running your new query this is the error


Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i had a "date" leftover...
select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4))) 
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op 
where convert(datetime, dh.date_despatched , 105) >= convert(datetime, '01-11-2007',105) 
and convert(datetime, dh.date_despatched , 105) < dateadd(day, 1, convert(datetime, '08-11-2007', 105))
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code

Open in new window

0
 
ammartahir1978Author Commented:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.


this is the error Angellll
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that means that some values in date_despatched are not in format DD-MM-YYYY,
please give details/data
0
 
ammartahir1978Author Commented:
what kind of detail data you want angelIII, i will provide that, sorry for the probelm
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the data of the field date_despatched ... I mean, can you check if all the rows have the format dd-mm-yyyy, resp is the field varchar or datetime.
0
 
ammartahir1978Author Commented:
how can i check the data type, as when i run the query

select date_despatched from [optdesphdr]

it comes up with

dd/mm/yy

example 23/11/00
22/11/99
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>how can i check the data type,
well, check the table design ...

dd/mm/yy is NOT the same as dd/mm/yyyy

in case the field is varchar:

select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4)))
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op
where convert(datetime, dh.date_despatched , 5) >= convert(datetime, '01-11-2007',105)
and convert(datetime, dh.date_despatched , 5) < dateadd(day, 1, convert(datetime, '08-11-2007', 105))
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code

in case the field is datetime

select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4)))
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op
where dh.date_despatched  >= convert(datetime, '01-11-2007',105)
and dh.date_despatched < dateadd(day, 1, convert(datetime, '08-11-2007', 105))
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code
0
 
ammartahir1978Author Commented:
both give the same error Angelll sorry to be a pain
0
 
ammartahir1978Author Commented:
this is the sample data from table

25/08/04
17/05/01
02/07/04
12/07/99
20/11/98
20/11/98
20/11/98
20/11/98
20/11/98
20/11/98
20/11/98
20/11/98
0
 
ammartahir1978Author Commented:
i have checked in the table and date_despatched is VARCHAR (255)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well that is dd/mm/yy and not dd-mm-yy ... then let's change the 5 into 3:

select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4)))
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op
where convert(datetime, dh.date_despatched , 3) >= convert(datetime, '01-11-2007',105)
and convert(datetime, dh.date_despatched , 3) < dateadd(day, 1, convert(datetime, '08-11-2007', 105))
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code
0
 
ammartahir1978Author Commented:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please run this:



select dh.date_despatched
from [optdesphdr]
where dh.date_despatched is not null
and dh.date_despatched <> ''
and dh.date_despatched not like '[0-2][0-9]/[0-1][0-9]/[0-9][0-9]'

Open in new window

0
 
ammartahir1978Author Commented:
this come up with 54880 rows, what you want me to do now
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show some of the rows that are returned?
0
 
imitchieCommented:
Angel, I think you keep missing the point. Read my last comment above. the fields are varchar.
0
 
imitchieCommented:
ammartahir1978, can you please run

select top 10 * from optdesphdr
0
 
imitchieCommented:
Just try this query

select  op.style_code, rl.return_code,Sum(cast(dl.col020 as decimal(20,4)))
from [optdesphdr] dh,[optdespline] dl,[optrtnline] rl,[optrtnhdr] rh,[optproduct] op
where right(dh.date_despatched,2)+substring(dh.date_despatched,4,2)+left(dh.date_despatched,2)
 between '071101' and '071108'
and dh.despatch_ref = dl.col001
and dl.col003 = rh.s_order_ref
and rh.return_ref = rl.return_ref
and rl.product_code = op.product_code  
group by op.style_code, rl.return_code
order by op.style_code
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Angel, I think you keep missing the point. Read my last comment above. the fields are varchar.
well, I have seen that. I could return that remark, that we need to see what "format" the data in the field actually is, it does not seem consistently in the same format...
0
 
imitchieCommented:
You are probably right, I believe it's consistently "3" dd/mm/yy, except maybe empty strings, causing problems for   convert(datetime, '', 3)
0
 
ammartahir1978Author Commented:
hi imitchie,

Your query worked perfectly fine, sorry for the delay i just got stuck in other stuff.


but yes you last query worked 100 %
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 17
  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now