CODEWITHSUNDEEP
sqlitesumwhere-clause
EAndrews
EAndrews

Reputation: 67

How to add a total column in SQLite

I am trying to run some analysis on sales data using SQLite.

At the moment, my table has several columns including a unique transaction ID, product name, quantity of that product and value of that product. For each transaction, there can be several records, because each distinct type of product in the basket has its own entry.

I would like to add two new columns to the table. The first one would be a total for each transaction ID which summed up the total quantity of all products in that basket.

I realize that there would be duplication in the table, as the repeated transaction IDs would all have the total. The second one would be similar but in value terms.

I unfortunately cannot do this by creating a new table with the values I want calculated in Excel, and then joining it to the original table, because there are too many records for Excel.

Is there a way to get SQL to do the equivalent of a sumif in Excel?

I was thinking something along the lines of:

select sum(qty) where uniqID = ...

But I am stumped by how to express that it needs to sum all quantities where the uniqID is the same as the one in that record.

Upvotes: 1

Views: 3752

Answers (1)

Graham Perks
Graham Perks

Reputation: 23398

You wouldn't create a column like that in SQL. You would simply query for the total on the fly. If you really wanted a table-like object, you could create a view that held 2 columns; uniqID and the sum for that ID.

Let's set up some dummy data in a table; column a is your uniqID, b is the values you're summing.

create table tab1 (a int, b int);
insert into tab1 values (1,1);
insert into tab1 values (1,2);
insert into tab1 values (2,10);
insert into tab1 values (2,20);

Now you can do simple queries for individual uniqIDs like this:

select sum(b) from tab1 where a = 2;
30

Or sum for all uniqIDs (the 'group by' clause might be all you're groping for:) :

select a, sum(b) from tab1 group by a;
1|3
2|30

Which could be wrapped as a view:

create view totals as select a, sum(b) from tab1 group by a;
select * from totals;
1|3
2|30

The view will update on the fly:

insert into tab1 values (2,30);
select * from totals;
1|3
2|60

In further queries, for analysis, you can use 'totals' just like you would a table.

Upvotes: 2

Related Questions