[Solved] UNION and Date field in a Query

[charlie.it]

Ciao to all,
In the database attached here, I don't understand why this code SQL works

Code: Select all

SELECT ' ', "In", "Out" FROM "Table1" ORDER BY "Date"
UNION 
SELECT 'Total', SUM( "In" ), SUM( "Out" ) FROM "Table1"

and this does't work:

Code: Select all

SELECT ' ',"Date", "In", "Out" FROM "Table1" ORDER BY "Date"
UNION 
SELECT 'Total',"Date", SUM( "In" ), SUM( "Out" ) FROM "Table1" 

It seems that waste the field type date.
Thanks

[Villeroy]

The GROUP BY clause is missing.

[Villeroy]

Just fill in a single constant date value (not a field of dates) like you fill in a single constant ' ' string in the other missing fileds.

Code: Select all

SELECT '',"Date", "In", "Out" FROM "Table1" ORDER BY "Date"
UNION
SELECT 'Total @',Current_date, SUM( "In" ), SUM( "Out" ) FROM "Table1" 

or make the GROUP BY clause obsolete by using an aggregated date:

Code: Select all

SELECT '',"Date", "In", "Out" FROM "Table1" ORDER BY "Date"
UNION
SELECT 'Total @',MAX("Date"), SUM( "In" ), SUM( "Out" ) FROM "Table1" 

[charlie.it]

I had not thought of Current_date or MAX () / MIN ().
Thank you Villeroy, you are always the best

[chrisb]

hello charlie.it,

although this topic is tagged as solved i would like to add a small tip.

data fields are selected & displayed from "Table1".
the UNION clause is used in order to display the summed values of the fields "In" & "Out" on a single row.
therefore as Villeroy has pointed out all selected values within the UNION clause must use either an aggregate function or be included in a group clause.

replacing "Date" with 'NULL' in the UNION clause may in your case provide a more appropriate result.

SELECT '', "Date", "In", "Out" FROM "Table1" ORDER BY "Date"
UNION
SELECT 'Total' , null, SUM( "In" ), SUM( "Out" ) FROM "Table1"

[charlie.it]

Thank you @chrisb, it works very well.