Using "UNION" in a SQL statement

[paperjam]

I'm trying to use the "union" between 2 sql statements, not sure how the group by feature should be placed, for example:

I'd like to put A next to B in a report.......

Select "field1", "field2", SUM("field3") AS "A"
From "table"
Where "field2"=1
UNION
Select "field1", "field2", SUM("field3") AS "B"
From "table"
Where "field2"=2
GROUP BY??????( I keep getting a response suggesting something in my "Select" statement isn't in my "Group BY" or visa versa
I'm using the same table and fields.

 

[IdoMillet]

You can't have different names for the same column position
in a UNION statement.

If you need different columns, considering using dummy expressions for the unmatched positions.

You are not showing what you are grouping by, but any non-aggregates column must appear in the Group By clause.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com