CODEWITHSUNDEEP
sqlsqlite
u4547878
u4547878

Reputation: 197

Working with sqlite sum() function properly

I am developing C# WPF with Sqlite database Project.

For Sqlite Database I have 3 Tablels. As follows:

Name:

id -INTEGER
name -TEXT

Table1:

nameID -INTEGER
amnt1   -INTEGER
amnt2   -REAL
amnt3   -INTEGER
amnt4   -INTEGER
amnt5   -INTEGER
amnt6   -INTEGER
entryDate   -TEXT

Table2:

id  -INTEGER
nameID -INTEGER
amnt1    -INTEGER
amnt2    -INTEGER
amnt3    -INTEGER
amnt4    -INTEGER
amnt5   -INTEGER
amnt6   -INTEGER
location    -INTEGER
entryDate   -TEXT

First i want to sum Table1's All columns with sum() function like:

select sum(amnt1),sum(amnt2),sum(amnt3),sum(amnt4),sum(amnt5),sum(amnt6) 
from Table1 
where entryDate between '2018-05-01' and '2018-05-20'

And table2 Sum:

select sum(amnt1),sum(amnt2),sum(amnt3),sum(amnt4),sum(amnt5),sum(amnt6) 
from Table2 
where entryDate between '2018-05-01' and '2018-05-20'

Now what i want to do is sum again all matching column of Table1 and Table2. I have tried like:

select sum(Table1.amnt1)+sum(Table2.amnt1),(Table1.amnt2)+sum(Table2.amnt2),sum(Table1.amnt3)+sum(Table2.amnt3),sum(Table1.amnt4)+sum(Table2.amnt4),sum(Table1.amnt5)+sum(Table2.amnt5),sum(Table1.amnt6)+sum(Table2.amnt6) 
from Table1 
join Table2 On Table1.nameID=Table2.nameID 
where Table1.entryDate between '2018-05-01' and '2018-05-20'

Perhaps my query is not correct, for this reason i am not getting correct result! What is the solution without making the query looks ugly?

Oh i tried to use SQL Fiddle but it sometime does not work, Still here is the link: http://sqlfiddle.com/#!5/9eecb7

In case sqlfilldle does not work, i have created table and inserted data as follows : 

CREATE TABLE `Name` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` INTEGER );
insert into `Name` VALUES(1,'test1');
insert into `Name` VALUES(2,'test2');


CREATE TABLE "Table1" ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `nameID` INTEGER, `amnt1` INTEGER, `amnt2` INTEGER, `amnt3` INTEGER, `amnt4` INTEGER, `amnt5` INTEGER, `amnt6` INTEGER, `entryDate` TEXT );
INSERT INTO Table1 VALUES(NULL,1,1,1,1,1,1,1,'2018-04-01');
INSERT INTO Table1 VALUES(NULL,2,1,1,1,1,1,1,'2018-05-01');
INSERT INTO Table1 VALUES(NULL,1,1,1,1,1,1,1,'2018-05-06');

CREATE TABLE "Table2" ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `nameID` INTEGER, `amnt1` INTEGER, `amnt2` INTEGER, `amnt3` INTEGER, `amnt4` INTEGER, `amnt5` INTEGER, `amnt6` INTEGER, `entryDate` TEXT );
INSERT INTO Table2 VALUES(NULL,1,1,1,1,1,1,1,'2018-04-02');
INSERT INTO Table2 VALUES(NULL,1,1,1,1,1,1,1,'2018-05-05');
INSERT INTO Table2 VALUES(NULL,2,1,1,1,1,1,1,'2018-05-06');

Upvotes: 0

Views: 586

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

I believe you need to use UNION ALL:

SELECT sum(amnt1),sum(amnt2),sum(amnt3),sum(amnt4),sum(amnt5),sum(amnt6) 
FROM (select amnt1,amnt2,amnt3,amnt4,amnt5,amnt6 
      FROM Table1 
      where entryDate between '2018-05-01' and '2018-05-20'
      UNION ALL 
      select amnt1,amnt2,amnt3,amnt4,amnt5,amnt6 
      FROM Table2 
      where entryDate between '2018-05-01' and '2018-05-20') sub

SQLFiddle Demo

Upvotes: 1

Related Questions