Solved

Error in aggregate function

Posted on 2007-11-29
35
270 Views
Last Modified: 2010-03-20
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
Comment
Question by:ammartahir1978
  • 17
  • 11
  • 7
35 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20372406
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
 

Author Comment

by:ammartahir1978
ID: 20372442
what this CAST used for just for my knowledge
0
 

Author Comment

by:ammartahir1978
ID: 20372447
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
 

Author Comment

by:ammartahir1978
ID: 20372455
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20372461
>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
 

Author Comment

by:ammartahir1978
ID: 20372500
can you please see the above comments as well AngelllI
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20372591
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
 

Author Comment

by:ammartahir1978
ID: 20372638
this is the error i get

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20372709
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
 

Author Comment

by:ammartahir1978
ID: 20372712
Server: Msg 195, Level 15, State 10, Line 4
'dateconvert' is not a recognized function name.

this is the error now
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20372714
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20372716
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
 

Author Comment

by:ammartahir1978
ID: 20372754
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
 

Author Comment

by:ammartahir1978
ID: 20372757
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20373123
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
 

Author Comment

by:ammartahir1978
ID: 20373179
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.


this is the error Angellll
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20373738
that means that some values in date_despatched are not in format DD-MM-YYYY,
please give details/data
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:ammartahir1978
ID: 20373987
what kind of detail data you want angelIII, i will provide that, sorry for the probelm
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20374105
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
 

Author Comment

by:ammartahir1978
ID: 20374256
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20374297
>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
 

Author Comment

by:ammartahir1978
ID: 20374523
both give the same error Angelll sorry to be a pain
0
 

Author Comment

by:ammartahir1978
ID: 20374544
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
 

Author Comment

by:ammartahir1978
ID: 20374582
i have checked in the table and date_despatched is VARCHAR (255)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20374616
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
 

Author Comment

by:ammartahir1978
ID: 20375031
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20375284
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
 

Author Comment

by:ammartahir1978
ID: 20375413
this come up with 54880 rows, what you want me to do now
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20375655
can you show some of the rows that are returned?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20377261
Angel, I think you keep missing the point. Read my last comment above. the fields are varchar.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20377273
ammartahir1978, can you please run

select top 10 * from optdesphdr
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20377285
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20377291
>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
 
LVL 25

Expert Comment

by:imitchie
ID: 20377378
You are probably right, I believe it's consistently "3" dd/mm/yy, except maybe empty strings, causing problems for   convert(datetime, '', 3)
0
 

Author Comment

by:ammartahir1978
ID: 20396306
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to fix this error 14 57
How to simplify my SQL statement? 14 50
SyBase Query Syntax Date Time conversion 4 26
Help writing a query 6 70
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now