Filter between date in sum by group in sql from two different tables data-CodePudding

How are you, I have two tables one tables is PRODUCT and other tables is STOCK_sales, i use sum and group by, But my problem is i can't not filter between dates.

in this link is my SQL database CODE and all structure how can i do this. myquery

SELECT 
    Product.ProductID,buyprice,sellprice, SUM(sellqty) as Total 
FROM product,Stock_sales 
WHERE Product.ProductID=Stock_sales.ProductID 
GROUP BY Product.ProductID,buyprice,sellprice 
ORDER BY Product.ProductID

THANK YOU.

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f2b78aab10cf770bc918206da6aaeecd

CodePudding user response：

You can use a where clause to filter the dates and other columns.

your FROM clause sould be wriiten by JOINS, your style is outdated for years

SELECT Product.ProductID,buyprice,sellprice,
SUM(sellqty) as Total
FROM product INNER JOIN Stock_sales ON Product.ProductID=Stock_sales.ProductID
WHERE data BETWEEN '2022-01-01' AND '2022-01-04'
GROUP BY Product.ProductID,buyprice,sellprice
ORDER BY Product.ProductID

productid | buyprice | sellprice | total
--------: | -------: | --------: | ----:
        1 |       10 |        20 |    20

db<>fiddle here

CodePudding user response：

I agree with the answer by nbk. However, if you want to continue with the WHERE clause specifying the key IDs instead of using a JOIN then you just need to extend that using AND:

WHERE Product.ProductID=Stock_sales.ProductID 
AND stock_sales.data = '2022-01-03'

WHERE Product.ProductID=Stock_sales.ProductID 
AND stock_sales.data BETWEEN '2022-01-03' AND '2022-01-17'