Re: Can SQL do this?
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Thu, 31 May 2007 06:41:35 -0400
That is why you should ALWAYS tell us what database you are using!
Besides pointing out that the KB article doesn't really appear to be
relevant (it's a different error message), I really cannot help with
foxpro - I don't even have it installed. You should post to a foxpro group,
probably this one: microsoft.public.fox.vfp.queries-sql
lowieann wrote:
Bob: I tried your code, but I was not able to get it to work.
I get this error message "[Microsoft] [ODBC Visual Foxpro Driver]
Syntax Error
So I checked on the Microsoft support website and found this:
http://support.microsoft.com/kb/191695
You receive an ODBC driver error message when you set a Left Outer
Join between two tables in Visual FoxPro
It provides a work around, but I don't understand it. Maybe you
could help?
The code I'm using is:
SQL = "Select q1.ID, q1.Code, q1.S, q2.Other, q2.OtherSum" & _
" From (select ID, Code, Sum(Value) as S From Table" & _
" Where Code<>'99' group by ID, Code) as q1" & _
" Left Outer Join (Select ID, Code as Other, Sum(Value) as
OtherSum" & _
" From Table where Code='99' group by ID, Code) as q2" & _
" On q1.Code=q2.Code"
Thanks for any help you can give me.
"lowieann" <lowieann@xxxxxxxxxxx> wrote in message
news:lvadnVTY5tZhAcHbnZ2dnUVZ_uKknZ2d@xxxxxxxxxxxxxx
Thanks Bob.
I'll try it in the AM.
Bruce
"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:OXN4wIfoHHA.3968@xxxxxxxxxxxxxxxxxxxxxxx
lowieann wrote:
I am using Microsoft ADO 2.8 with VB6 and would to know if SQL can
do this.
By "SQL", do you mean SQL Server? if so, which version?
One table that looks like this.
ID Code Value
01 01 1.00
01 01 2.00
01 99 1.50
01 99 1.25
02 02 1.00
03 34 3.50
03 99 1.25
I would like to sum the values and have the returned record set to
look like this:
ID Code Sum Other Sum
You cannot have two columns with the same name ...
01 01 3.00 99 2.75
02 02 1.00
03 34 3.50 99 1.25
The point is to have the sum of Code 99 be part of the returned
recordset of ID's, in this case ID=01 and 03.
Assuming SQL Server 7 and above, try this (untested, but it should
give you the direction you need):
select q1.ID, q1.Code, q1.[Sum],q2.Other,OtherSum FROM
(select ID, Code, Sum(Value) As [Sum]
From Table
where Code <> '99'
group by ID, Code) q1
LEFT JOIN
(select ID, Code As Other, Sum(Value) As OtherSum
From Table
where Code = '99'
group by ID, Code) q2
ON q1.Code = q2.Code
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
.
- References:
- Can SQL do this?
- From: lowieann
- Re: Can SQL do this?
- From: Bob Barrows [MVP]
- Re: Can SQL do this?
- From: lowieann
- Re: Can SQL do this?
- From: lowieann
- Can SQL do this?
- Prev by Date: Re: Can SQL do this?
- Previous by thread: Re: Can SQL do this?
- Next by thread: Re: Mdac 2.8 para Windows Vista
- Index(es):
Relevant Pages
|
|
