Filter between dates grouping 3 tables in SQL Server

Question

I have this SQL in SQL Server:

SELECT 
    Itens.Mercadoria, Mercadoria.Nome, Cabecalho.Data, 
    SUM(ValorUnitario) AS Total, 
    SUM(Quantidade) AS Quantidade
FROM 
    Itens 
INNER JOIN 
    Mercadoria ON Itens.Mercadoria = Mercadoria.Codigo 
INNER JOIN 
    Cabecalho ON Cabecalho.Codigo = Itens.Cabecalho 
WHERE 
    Cabecalho.Data >= '2016-01-01' 
    AND Cabecalho.Data <= '2018-12-31'
GROUP BY 
    Itens.Mercadoria, Mercadoria.Nome, Cabecalho.Data 
ORDER BY 
    4 DESC

It is returning the following result.

The highlighted values are repeating, I do not want to be repeated, I want to show only once each item and that the Quantidade and Total fields are SUM.

For example:

`Camisa Polo` -> **Quantidade = 23**
`Calça Jeans` -> **Quantidade = 15** 
`Camiseta Estampada` -> **Quantidade = 21**

You say "having to filter", if with that you don't mean the HAVING sentence of SQL then you should check this... SQL query to select dates between two dates https://stackoverflow.com/q/5125076/2507654 — alex, Sep 10 '17 at 06:24
How do these tables relate to eachother? I see IdProduct in SaleItem, but what connects SaleItems to Sales? — Zohar Peled, Sep 10 '17 at 06:40
I edited the question and I think it was easier to understand with the examples. — Anderson, Sep 10 '17 at 06:42

score 1 · Accepted Answer · answered Sep 10 '17 at 06:43

Assuming thate the relation between Sales and SaleItems is based on SalesId you can use between assign to your_start_date and your_end_date a proper value

  select  Products.ProductName 
        , sum(SaleItems.Price)
        , sum(SaleItems.Quantity)
  from  Products
  inner join SaleItems on SaleItems.IdProduct = Products.IdProduct
  inner join Sales on Sales.IdSale = SaleItems.IdSale
  where SaleDate between your_start_date and your_end_date
  group by  Products.ProductName

In you case remove or aggregated the Cabecalho.Data column eg:

  SELECT Itens.Mercadoria
      ,  Mercadoria.Nome
      , SUM(ValorUnitario) AS Total
      , SUM(Quantidade) AS Quantidade
  FROM Itens INNER JOIN Mercadoria ON Itens.Mercadoria = Mercadoria.Codigo 
  INNER JOIN Cabecalho ON Cabecalho.Codigo = Itens.Cabecalho 
  WHERE Cabecalho.Data between   '2016-01-01' AND  '2018-12-31'
  GROUP BY Itens.Mercadoria, Mercadoria.Nome
  ORDER BY 4 DESC

or

  SELECT Itens.Mercadoria
      ,  Mercadoria.Nome
      ,  max(Cabecalho.Data)
      , SUM(ValorUnitario) AS Total
      , SUM(Quantidade) AS Quantidade
  FROM Itens INNER JOIN Mercadoria ON Itens.Mercadoria = Mercadoria.Codigo 
  INNER JOIN Cabecalho ON Cabecalho.Codigo = Itens.Cabecalho 
  WHERE Cabecalho.Data between   '2016-01-01' AND  '2018-12-31'
  GROUP BY Itens.Mercadoria, Mercadoria.Nome 
  ORDER BY 4 DESC

Filter between dates grouping 3 tables in SQL Server

1 Answers1