Group by isnt calculating quantity and price?

user_15181382 0
Viewed 71

I am trying to export sales report from table 'sales'

It has field names pname, price, sold_to, pquantity, pamount, date

Here I don't wish to display the same company I sold_to over and over if the company purchased same product on 2021-02-13 so I tried to group by the query but then it isn't showing total sum of pquantity and pamount in results even though it groups same company.

could someone help me out with fixing the query?

Here is my MYSQL query:

    SELECT * FROM sales 
    WHERE DATE_FORMAT(date, '%Y-%m-%d') BETWEEN '$from' AND '$to' 
    GROUP BY pname, sold_to, pquantity, pamount, date 
    ORDER BY sale_id DESC

I am definitely doing it wrong would appreciate if someone could help me correct the logic.

mysql sql
1 Answers

You need to use SUM() to get sum of quantity and amount

SELECT pname, sold_to, date, SUM(pquantity), SUM(pamount) 
FROM sales 
WHERE DATE_FORMAT(date, '%Y-%m-%d') BETWEEN '$from' AND '$to' 
GROUP BY pname, sold_to, date 
ORDER BY sale_id DESC
user_54602610
Related