Re: Can SQL do this?

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.






.

Relevant Pages

  • Re: Can SQL do this?
    ... do you mean SQL Server? ... ID Code Sum Other Sum ... Please reply to the newsgroup. ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: Can SQL do this?
    ... do you mean SQL Server? ... I would like to sum the values and have the returned record set to ... ID Code Sum Other Sum ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Re: Can SQL do this?
    ... do you mean SQL Server? ... I would like to sum the values and have the returned record set to ... ID Code Sum Other Sum ... Please reply to the newsgroup. ...
    (microsoft.public.data.ado)
  • Re: Automatically Update Form Text Feild Calculations
    ... > I am preparing a form in Word. ... I have a table and the last row i want to sum ... > i have other forms that i have written and used this exact method and they ... This reply is posted in the Newsgroup; please post any follow question or reply ...
    (microsoft.public.word.docmanagement)
  • Re: [SQL query] Generate a report from events?
    ... sum of device #1 between midnight and 10:00: ... DECLARE @endtime datetime; ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)