Need help with a UNION statement -SUM

[Windsailor]

I have one table that I want to pull data from it only if a ‘Bit’ column is true.
This applies to two similar instances in the same table.
So I created a UNION query to pull the desired data.

SELECT [Query1-1].ID, [Query1-1].A1, [Query1-1].ABit
FROM [Query1-1]
WHERE ((([Query1-1].ABit)=True));
UNION SELECT [Query1-2].ID, [Query1-2].B1, [Query1-2].BBit
FROM [Query1-2]
WHERE ((([Query1-2].BBit)=True));

The problem is that I want to add –or sum- the like columns (that have the same ID value) within that UNION statement…

ID A1 ABit
2 35 -1
3 41 -1
4 52 -1
4 62 -1

To make…

ID A1 ABit
2 35 -1
3 41 -1
4 114 -1

Am I going in the wrong direction here?

 

[Windsailor]

Let me re-word this...

I want to add two column values (same table, same row) only if each 'Bit' column for each column I want to add is true...

Did that make sense?
For instance...

ID     A1     ABit     B1     BBit
1       5     True     10    False
2      10     True     15    True

To:

ID     'A1'     
1       5     
2      25

This one's bugging me...

 

[Windsailor]

Ahhh... never mind.
I'm going to change things...
I will leave it up in case someone wants to answer it anyway.

Edit---

I believe the answer is to use the IIF function in the conditional 'add' statement.
That should fly ok...

Public Function IIf( _
ByVal Expression As Boolean, _
ByVal TruePart As Object, _
ByVal FalsePart As Object _
) As Object

That should solve it.

 

[cjard]

I have one table that I want to pull data from it only if a ‘Bit’ column is true.
This applies to two similar instances in the same table.
So I created a UNION query to pull the desired data.

Wrong forum, i'm afraid - theis is ADO.NET (a client side dataase access technology), it doesnt deal with SQL queries of whatever RDBMS youre using.

The problem is that I want to add –or sum- the like columns (that have the same ID value) within that UNION statement…

[B]SELECT ID, SUM(A1), ABit
FROM
([/B]
SELECT [Query1-1].ID, [Query1-1].A1, [Query1-1].ABit
FROM [Query1-1]
WHERE ((([Query1-1].ABit)=True));
UNION SELECT [Query1-2].ID, [Query1-2].B1, [Query1-2].BBit
FROM [Query1-2]
WHERE ((([Query1-2].BBit)=True));
[B]) dumb_alias
GROUP BY ID, ABit[/B]

 

[cjard]

Let me re-word this...

I want to add two column values (same table, same row) only if each 'Bit' column for each column I want to add is true...

Did that make sense?
For instance...

ID     A1     ABit     B1     BBit
1       5     True     10    False
2      10     True     15    True

To:

ID     'A1'     
1       5     
2      25

This one's bugging me...

SELECT 
  COALESCE(q1.ID, q2.ID) as ID, 
  SUM(COALESCE(q1.A1, 0) + COALESCE(q2.A1, 0))
FROM 
  (SELECT ID, SUM(A1) as A1 FROM [Query1-1] WHERE ABit = TRUE GROUP BY ID) q1 
  FULL OUTER JOIN 
  (SELECT ID, SUM(A1) as A1 FROM [Query1-2] WHERE ABit = TRUE GROUP BY ID) q2 
  ON 
    q1.ID = q2.ID
GROUP BY 
  COALESCE(q1.ID, q2.ID)

 

[Windsailor]

Many thanks cjard...
Glad your still hanging around here...

Sorry... didn't see a SQL forum here... or a LINQ either... that one will be needed shortly.

 

[cjard]

LiINQ is something I should look into too