adventuresncode adventuresncode - 2 months ago 7
SQL Question

How to sum total from two different variables in the same column

I want to sum the volume for x stocks on all days.
My table (stockData) looks like this:

DATE SYMBOL PRICE VOLUME
2016-09-21 AAP 146.33 1116964
2016-09-21 AMZN 789.74 2718644
2016-09-21 AN 47.62 1025741
2016-09-21 AZO 751.45 592877
2016-09-21 BBBY 43.11 3504367
2016-09-21 CBS 50.08 7149860
2016-09-21 CCL 45.82 4510931
2016-09-21 CMG 399.75 924212
2016-09-21 COH 36.03 3181106
2016-09-21 DG 72.18 3098500
2016-09-20 AAP 145.89 1197144
2016-09-20 AMZN 780.22 1937164
2016-09-20 AN 48 459415
2016-09-20 AZO 744.06 362168
2016-09-20 BBBY 43.05 2198117
2016-09-20 CBS 49.97 8364371
2016-09-20 CCL 46.19 4927654
2016-09-20 CMG 400.42 1490731
2016-09-20 COH 35.84 2230159
2016-09-20 DG 71.72 2792748
2016-09-19 AAP 148.44 972161
2016-09-19 AMZN 775.1 2297221
2016-09-19 AN 48.38 797548
2016-09-19 AZO 746.35 382199
2016-09-19 BBBY 43.34 2067412
2016-09-19 CBS 51.08 3319275
2016-09-19 CCL 45.9 4709323
2016-09-19 CMG 402.76 1139757
2016-09-19 COH 35.94 4287905
2016-09-19 DG 71.83 4096195


I want to know the total value traded each day for AMZN and COH combined.

The result should look as following:

Date ValueTraded
2016-09-21 2,261,637,161.74
2016-09-20 1,591,342,994.64
2016-09-19 1,934,683,302.80


I am using dbBrowser for SQLite

I can return a new column called ValueTraded within the select statement, but I cannot figure out where to put the sum, any help would be appreciated.

select Date, Price * Volume as ValueTraded
from stockData
where symbol IN ("AMZN", "COH")

Answer

You just need sum() and group by:

select Date, sum(Price * Volume) as ValueTraded
from stockData
where symbol in ('AMZN', 'COH')
group by date;
Comments