B4J Question [Solved] sql problem (Union,Group By date)

[behnam_tr]

hi
i need help for write query
According to the picture below, I have 3 tables
Now I want to take data from three tables and display it in ascending order in terms of day, month and year
All dates are stored in the original database as a tick (datetime.now)

I wrote this code but it is not my desired result

SELECT 'tb1' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
SUM( value ) AS total FROM tb1
GROUP BY year,month,day

UNION

SELECT 'tb2' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
SUM( value ) AS total FROM tb2
GROUP BY year,month,day

UNION

SELECT 'tb3' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
SUM( value ) AS total FROM tb3
GROUP BY year,month,day

ORDER BY date asc

thanks

 

[EnriqueGonzalez]

may be:

SELECT year
          , month 
         , day
, sum(tb1) as tb1
, sum(tb2) as tb2
, sum(tb3) as tb3
, (sum(tb1) + sum(tb2) + sum(tb3)) as total
FROM (
SELECT 'tb1' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
value AS tb1,
0 tb2,
0 tb3,
FROM tb1
GROUP BY year,month,day

UNION

SELECT 'tb2' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
0 tb1,
SUM( value ) AS tb2
0 tb3
FROM tb2

UNION

SELECT 'tb3' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
0 tb1,
0 tb2,
value AS tb3
 FROM tb3
) as x
GROUP BY year,month,day
ORDER BY year month, day

 

[behnam_tr]

may be:

SELECT year
          , month
         , day
, sum(tb1) as tb1
, sum(tb2) as tb2
, sum(tb3) as tb3
, (sum(tb1) + sum(tb2) + sum(tb3)) as total
FROM (
SELECT 'tb1' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
value AS tb1,
0 tb2,
0 tb3,
FROM tb1
GROUP BY year,month,day

UNION

SELECT 'tb2' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
0 tb1,
SUM( value ) AS tb2
0 tb3
FROM tb2

UNION

SELECT 'tb3' as tp,date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
0 tb1,
0 tb2,
value AS tb3
 FROM tb3
) as x
GROUP BY year,month,day
ORDER BY year month, day

thanks man
perfect...
i edite some code and i think it done

SELECT date,
year,
month ,
day,
sum(s1) as tb1,
sum(s2) as tb2,
sum(s3) as tb3,
(sum(s1) + sum(s2) + sum(s3)) as total
FROM
(SELECT date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
value AS s1,
0 as s2,
0 as s3
FROM tb1


UNION

SELECT date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
0 AS s1,
value as s2,
0 as s3
FROM tb2


UNION

SELECT date,
strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month ,
strftime('%d', date / 1000, 'unixepoch') as day ,
0 AS s1,
0 as s2,
value as s3
FROM tb3

) as x
GROUP BY year,month,day
ORDER BY date asc