Re: Can SQL do this?

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?
    ... So I checked on the Microsoft support website and found this: ... do you mean SQL Server? ... 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: 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: [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)
  • Re: SQL Query
    ... You are trying to do numerical math on character strings. ... Select sum ) total_singles from teamstats. ... I support the Professional Association for SQL Server ... > The sum or average aggregate operation cannot take a varchar data type as ...
    (microsoft.public.sqlserver.server)