grouping sales aggregate by month using SQLite sandbox in Julia


February 2019


23 time


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.


0 answers