Solved

Error in aggregate function

Posted on 2007-11-29
35
269 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]
Comment Utility
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
Comment Utility
what this CAST used for just for my knowledge
0
 

Author Comment

by:ammartahir1978
Comment Utility
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
Comment Utility
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]
Comment Utility
>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
Comment Utility
can you please see the above comments as well AngelllI
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
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]
Comment Utility
that means that some values in date_despatched are not in format DD-MM-YYYY,
please give details/data
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ammartahir1978
Comment Utility
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]
Comment Utility
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
Comment Utility
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]
Comment Utility
>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
Comment Utility
both give the same error Angelll sorry to be a pain
0
 

Author Comment

by:ammartahir1978
Comment Utility
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
Comment Utility
i have checked in the table and date_despatched is VARCHAR (255)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
this come up with 54880 rows, what you want me to do now
0
 
LVL 142

Expert Comment

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

Expert Comment

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

Expert Comment

by:imitchie
Comment Utility
ammartahir1978, can you please run

select top 10 * from optdesphdr
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
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]
Comment Utility
>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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

7 Experts available now in Live!

Get 1:1 Help Now