CODEWITHSUNDEEP
mysql
batwing
batwing

Reputation: 247

MySql get Sum Price and Order Count based on Month?

I have table like this 

+-------------+---------------------+---------------+--------------+
| Customer    | Date                | OrderId       | Amount       |
+-------------+---------------------+---------------+--------------+
|Jake         | 01-08-2019 19:32:44 | 001           | 10           |
|Smith        | 01-09-2019 22:33:49 | 002           | 5            |
|Jake         | 21-10-2019 22:08:24 | 003           | 5            |
|Brad         | 12-10-2019 22:33:57 | 004           | 15           |
|Brad         | 24-10-2019 22:37:34 | 005           | 15           |
|Jake         | 14-10-2019 15:23:49 | 006           | 5            |
+-------------+---------------------+---------------+--------------+

I want to show my result like customer name, Month, Count of order, Sum of amount. Exactly like below

+-------------+---------------------+---------------+--------------+
| Customer    | Month               | No. of Order  | Total Amount |
+-------------+---------------------+---------------+--------------+
|Jake         | Aug                 | 1             | 10           |
|Smith        | Sept                | 1             | 5            |
|Jake         | Oct                 | 2             | 10           |
|Brad         | Oct                 | 2             | 30           |
+-------------+---------------------+---------------+--------------+

This is what I tried before but couldn't get the result I wanted. 

SELECT lc.CustomerName
     , month(o.oDate)
     , count(lo.OrderId)
     , sum(b.ProductBillAmount) 
  FROM advation_tmp.js_labelorders lo
  join js_labelcustomers lc 
    on lc.Id = lo.LabelCustomerId
  join js_billingdetail b 
    on b.OrderID = lo.OrderId
  join js_orderdetails o 
    on o.ID = b.OrderID 
 where o.oDate <= '2020-02-06 13:00:00' 
   and o.oDate >= '2019-07-06 13:00:00';

Upvotes: 0

Views: 106

Answers (1)

juergen d
juergen d

Reputation: 204756

You can't mix column selects and aggregated columns. You need to group your data like this

SELECT lc.CustomerName, month(o.oDate), count(lo.OrderId), sum(b.ProductBillAmount) 
FROM advation_tmp.js_labelorders lo 
inner join js_labelcustomers lc on lc.Id = lo.LabelCustomerId 
inner join js_billingdetail b on b.OrderID = lo.OrderId 
inner join js_orderdetails o on o.ID = b.OrderID 
where o.oDate <= '2020-02-06 13:00:00' and o.oDate >= '2019-07-06 13:00:00'
group by lc.CustomerName, month(o.oDate)

Upvotes: 2

Related Questions