I am using the SQLite package in Julia. I need to aggregate the sum of sales by months. I have 3 tables Order, Order items and products.
I have tried a few queries but nothing seems to work.
DataFrame(SQLite.Query(acme, """select strftime('%m',OrderDate)as month, sum(price*quantity) as sales from orders join orderitem on orders.OrderID = orderitem.OrderID join product on product.productid = orderitem.productid group by month order by sales"""))
And this is what I am getting as a result!!
month sales Any Float64⍰ 1 missing 3.86424e5
Also I tried this, but it didn't work
select sum(product.Price * orderitem.Quantity) as Sales, month(orders.OrderDate) as month from product inner join orderitem on product.ProductID = orderitem.ProductID inner join orders on orderitem.OrderID = orders.OrderID group by Month;
Thanks for your help - and I hope my code looks more readable.