Doubt with registration grouping

Asked

Viewed 75 times

1

In my example, I have a query that return 3 products and the same were purchased in different orders but I do not need this order number. So I did it this way:

SELECT 
  c.prod_pk_id,           
 '61068276007702' as CNPJ,  
 'teste' AS FABRICANTE,  
 c.prod_pk_id AS CODIGOPRODUTO,  
 c.prod_nome AS NOMEPRODUTO,  
 case                         
   when CAST(CAST(c.PROD_COD_BARRAS AS BIGINT) AS VARCHAR)  = '0' then C.PROD_CODIGO_EXTERNO   
   when CAST(CAST(c.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) <> ''  then CAST(CAST(c.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) end as CODIGOBARRAS,                                  
   (b.pedip_qtde * 1000) AS QTDVENDIDO,  'BRL' as MOEDA,  
   CAST(a.pedi_dt_compra AS DATE) as DATA_VENDA,  
   b.PEDIP_VALOR_POR as TOTAL_PRODUTO,  
   ( cast( b.PEDIP_VALOR_POR  * b.pedip_qtde as Int) * 1000) AS TOTAL_PEDIDO,   
   (  select sum(PRODP_QTDE_ESTOQUE * 1000) from TB_PRODUTO_PRECO  where PRODP_FK_PROD_ID = c.prod_pk_id  ) as ESTOQUETOTAL     
   FROM TB_PEDIDO  A  
   INNER JOIN TB_CLIENTE G ON G.CLIE_PK_ID = A.PEDI_FK_CLIE_ID  
   inner join TB_PEDIDO_PRODUTO b on a.PEDI_PK_ID = b.PEDIP_FK_PEDI_ID  
   inner join TB_PRODUTO C on c.prod_pk_id = b.PEDIP_FK_PROD  
   inner join TB_FABRICANTE d on d.FABR_PK_ID = c.PROD_FK_FABR_ID  
   where d.fabr_nome like '%teste%'  
   and A.pedi_dt_compra between '2017-01-02' and '2017-01-03' 
   and c.prod_pk_id = 62168
   group by c.prod_pk_id,  
c.prod_nome,c.PROD_COD_BARRAS,C.PROD_CODIGO_EXTERNO,b.pedip_qtde,a.pedi_dt_compra,b.PEDIP_VALOR_POR,d.fabr_nome

me returns the records, I would like to group them:

inserir a descrição da imagem aqui

sql sql-server

  • To be able to group, the content of the lines must be equal. If despite the column is contained in the GROUP BY clause and the bank does not respect it is a sign that has different content between lines. You will need to remove this column from select and group by

  • @Reginaldorigo, the content is equal, see in the image the result, the order number is not in the query

  • Select from this select. Like this: SELECT * FROM ( select ... ) A GROUP BY .. all fields that come.

  • Queries that have GROUP BY clause with all columns usually indicate code that needs to be rewritten. In your case, I suggest that you group orders for the same product before making the other joints. In this way, the GROUP BY clause will be kept to a minimum.

  • Is it also to group the orders for the same product if, within the reporting period, the orders for the same product are made on different days? How is the pedi_dt_purchase column declared? If as datetime, it contains only date or date and time?

3 answers

0

This is the first proposal to combine applications for the same product on the same date. Only after this grouping is the query executed.

-- código #1 v3
with Pedidos_Produto as (
SELECT PdPr.PEDIP_FK_PROD, 
       Cast(PdPr.pedi_dt_compra as date) as DataVenda,
       Sum(PdPr.pedip_qtde) as QtdVendido,
       Sum(PdPr.pedip_qtde * PdPr.PEDIP_VALOR_POR) as TotalVendido
  from TB_PEDIDO as Pd
       inner join TB_PEDIDO_PRODUTO as PdPr on Pd.PEDI_PK_ID = PdPr.PEDIP_FK_PEDI_ID
  where Pd.pedi_dt_compra between '2017-01-02' and '2017-01-03' 
  group by PdPr.PEDIP_FK_PROD, Cast(PdPr.pedi_dt_compra as date)
)
SELECT F.fabr_nome as Fabricante,
       Pr.prod_pk_id as [Código do produto],  
       Pr.prod_nome as [Nome do produto],  
       case when CAST(CAST(Pr.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) <> ''  
            then CAST(CAST(Pr.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) end as [Código de barras],
       PP.DataVenda, PP.QtdVendido, PP.TotalVendido,
       'BRL' as Moeda,
       (SELECT Sum(PRODP_QTDE_ESTOQUE) 
          from TB_PRODUTO_PRECO 
          where PRODP_FK_PROD_ID = Pr.prod_pk_id) as [Estoque total]
  from Pedidos_Produto as PP
       inner join TB_PRODUTO as Pr on Pr.prod_pk_id = PP.PEDIP_FK_PROD
       inner join TB_FABRICANTE as F on F.FABR_PK_ID = Pr.PROD_FK_FABR_ID;

0

Good night all right ?

See how it should be done.

I also changed the total stock, as it was done, for each record that returned in select it did select to see the stock, changed to work with a data set that is much more efficient.

SELECT 
C.PROD_PK_ID,           
'61068276007702' AS CNPJ,  
'TESTE' AS FABRICANTE,  
C.PROD_PK_ID AS CODIGOPRODUTO,  
C.PROD_NOME AS NOMEPRODUTO,  
CASE                         
    WHEN CAST(CAST(C.PROD_COD_BARRAS AS BIGINT) AS VARCHAR)  = '0' THEN 
        C.PROD_CODIGO_EXTERNO   
    WHEN CAST(CAST(C.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) <> ''  THEN 
        CAST(CAST(C.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) 
END AS CODIGOBARRAS,                                  
(B.PEDIP_QTDE * 1000) AS QTDVENDIDO,  
'BRL' AS MOEDA,  
CAST(A.PEDI_DT_COMPRA AS DATE) AS DATA_VENDA,  
B.PEDIP_VALOR_POR AS TOTAL_PRODUTO,  
( CAST( B.PEDIP_VALOR_POR  * B.PEDIP_QTDE AS INT) * 1000) AS TOTAL_PEDIDO,   
TV_ESTOQUE.ESTOQUETOTAL
FROM TB_PEDIDO  A  
INNER JOIN TB_CLIENTE G ON G.CLIE_PK_ID = A.PEDI_FK_CLIE_ID  
INNER JOIN TB_PEDIDO_PRODUTO B ON A.PEDI_PK_ID = B.PEDIP_FK_PEDI_ID  
INNER JOIN TB_PRODUTO C ON C.PROD_PK_ID = B.PEDIP_FK_PROD  
INNER JOIN TB_FABRICANTE D ON D.FABR_PK_ID = C.PROD_FK_FABR_ID
INNER JOIN (
            SELECT SUM(PRODP_QTDE_ESTOQUE * 1000) AS ESTOQUETOTAL,   PRODP_FK_PROD_ID 
            FROM TB_PRODUTO_PRECO               
          ) TV_ESTOQUE ON (TV_ESTOQUE.PRODP_FK_PROD_ID = C.PROD_PK_ID ) 
WHERE D.FABR_NOME LIKE '%TESTE%'  
AND A.PEDI_DT_COMPRA BETWEEN '2017-01-02' AND '2017-01-03' 
AND C.PROD_PK_ID = 62168
GROUP BY C.PROD_PK_ID, C.PROD_PK_ID, C.PROD_NOME , CASE                         
    WHEN CAST(CAST(C.PROD_COD_BARRAS AS BIGINT) AS VARCHAR)  = '0' THEN 
        C.PROD_CODIGO_EXTERNO   
    WHEN CAST(CAST(C.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) <> ''  THEN 
        CAST(CAST(C.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) 
END, A.PEDI_DT_COMPRA, B.PEDIP_VALOR_POR, TV_ESTOQUE.ESTOQUETOTA

0

A sub select solves your problem.

select * from
(
    SELECT 
      c.prod_pk_id,           
     '61068276007702' as CNPJ,  
     'teste' AS FABRICANTE,  
     c.prod_pk_id AS CODIGOPRODUTO,  
     c.prod_nome AS NOMEPRODUTO,  
     case                         
       when CAST(CAST(c.PROD_COD_BARRAS AS BIGINT) AS VARCHAR)  = '0' then C.PROD_CODIGO_EXTERNO   
       when CAST(CAST(c.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) <> ''  then CAST(CAST(c.PROD_COD_BARRAS AS BIGINT) AS VARCHAR) end as CODIGOBARRAS,                                  
       (b.pedip_qtde * 1000) AS QTDVENDIDO,  'BRL' as MOEDA,  
       CAST(a.pedi_dt_compra AS DATE) as DATA_VENDA,  
       b.PEDIP_VALOR_POR as TOTAL_PRODUTO,  
       ( cast( b.PEDIP_VALOR_POR  * b.pedip_qtde as Int) * 1000) AS TOTAL_PEDIDO,   
       (  select sum(PRODP_QTDE_ESTOQUE * 1000) from TB_PRODUTO_PRECO  where PRODP_FK_PROD_ID = c.prod_pk_id  ) as ESTOQUETOTAL     
       FROM TB_PEDIDO  A  
       INNER JOIN TB_CLIENTE G ON G.CLIE_PK_ID = A.PEDI_FK_CLIE_ID  
       inner join TB_PEDIDO_PRODUTO b on a.PEDI_PK_ID = b.PEDIP_FK_PEDI_ID  
       inner join TB_PRODUTO C on c.prod_pk_id = b.PEDIP_FK_PROD  
       inner join TB_FABRICANTE d on d.FABR_PK_ID = c.PROD_FK_FABR_ID  
       where d.fabr_nome like '%teste%'  
       and A.pedi_dt_compra between '2017-01-02' and '2017-01-03' 
       and c.prod_pk_id = 62168
)c
group by c.prod_pk_id, c.CNPJ, c.FABRICANTE, c.CODIGOPRODUTO, 
c.NOMEPRODUTO, c.CODIGOBARRAS, c.QTDVENDIDO, c.MOEDA, c.DATA_VENDA, c.TOTAL_PRODUTO,
c.TOTAL_PEDIDO, c.ESTOQUETOTAL

See if I haven’t forgotten any field in group by.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.