Perguntas Ajuda com uma Query

Galera, boa tarde !!

Estou tentando colocar na minha query um contador de numero de linhas de ocorrência mas não estou conseguindo...
Se alguém puder dar uma luz fico agradecido...

Preciso colocar a instrução da primeira query na segunda porém o SQL Server 2008 não aceita o group by na instrução, e preciso ratear o valor das colunas Culi, Merc e DF pelo numero de ocorrencias que a query retorna :

1ª Query -

SELECT D2_CLIENTE,D2_LOJA,COUNT(D2_CLIENTE+D2_LOJA) QTDE FROM SD2010 WHERE D2_EMISSAO BETWEEN '20130301' AND '20130331' AND SD2010.D_E_L_E_T_ = ' ' GROUP BY D2_CLIENTE,D2_LOJA

2ª Query -

SELECT D2_FILIAL AS FL,D2_DOC AS NF,D2_SERIE AS SERIE,D2_EST,
SUBSTRING(D2_EMISSAO,7,2)+'/'+SUBSTRING(D2_EMISSAO,5,2)+'/'+SUBSTRING(D2_EMISSAO,1,4) AS EMISSAO,D2_COD,B1_DESC,
D2_CLIENTE+'/'+D2_LOJA AS CLIENTE,
A1_NOME,A1_SATIV1 ATIV1,
ISNULL (( SELECT X5_DESCRI FROM SX5010 WHERE X5_TABELA = 'Z5' AND X5_CHAVE = A1_SATIV1),'COD INCORRETO') AS DESCR1 ,
A1_SATIV2 ATIV2,
ISNULL (( SELECT X5_DESCRI FROM SX5010 WHERE X5_TABELA = 'Z8' AND X5_CHAVE = A1_SATIV2),' ') AS DESCR2 ,
A3_COD AS VEND,A3_NOME,
PERC_COMIS = Replace(Convert(Decimal(12,4),( CASE WHEN A3_COD IN ('NS','NS2','ALIS') THEN 0.0050
WHEN A3_COD IN ('KENJI','MG') THEN B1_COMIS
ELSE (B1_COMIS) END )), '.',','),
D2_CF AS CF,
SUM(D2_QUANT) AS QTOTAL,
SUM(D2_QUANT * B1_PESO) AS PESO,
SUM(D2_TOTAL) AS VITEM,
CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911') THEN replace(Convert(Decimal(12,2), SUM(D2_TOTAL )),'.',',') ELSE replace(Convert(Decimal(12,2), 0),'.',',') END AS VTOTAL,

Replace(convert(Decimal(6,2),B1_CUSTVDA),'.',',') AS CUSTO_VD,
Replace(Convert(Decimal(12,2),SUM(B1_CUSTVDA*D2_QUANT)),'.',',') AS CUSTOREAL,

Replace(convert(Decimal(6,2),B1_CUSTPRI),'.',',') AS CUS_PRIM,
Replace(Convert(Decimal(12,2),SUM(B1_CUSTPRI*D2_QUANT)),'.',',') AS CUSTOPRIM,
C5_TPFRETE AS TIPO_FRETE,
Replace(Convert(Decimal(6,2),CC2_XPERFR),'.',',') AS PERC_FRET,
Replace(Convert(Decimal(12,2),SUM(((CASE C5_TPFRETE WHEN 'C' THEN (D2_TOTAL*CC2_XPERFR)/100 ELSE 0 END)))),'.',',') FRETE ,
Replace(Convert(Decimal(12,2),SUM(D2_VALIPI)),'.',',') VALIPI,
Replace(Convert(Decimal(12,2),SUM(D2_VALICM)),'.',',') VALICM,
A1_DESCDUP AS PERC_CONTRATO,
Replace(Convert(Decimal(12,2),SUM(D2_TOTAL*(A1_DESCDUP/100))),'.',',') DESC_DUP,
PIS = SUM(D2_TOTAL)*0.0165,
COFINS = SUM(D2_TOTAL)*0.0760,
OUTROS_IMP = CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911','5551','5556','5201','5905','5915','7949','6913','5949','6949','5102','6109') THEN SUM(D2_TOTAL*0.031) ELSE 0 END,
COMISSAO = ISNULL(SUM(CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911')
THEN ( CASE WHEN A3_COD IN ('NS','NS2','ALIS') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*0.0050
WHEN A3_COD IN ('KENJI','MG') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*B1_COMIS
ELSE (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*(B1_COMIS) END ) END ),0) ,

CULI = ( SELECT ISNULL ( SUM( CASE WHEN ( Z02_STATUS = 1 ) THEN Z02_VALOR ELSE 0 END ), 0 )
from Z02010 WHERE Z02_DATA BETWEEN '20130301' AND '20130331' AND Z02010.D_E_L_E_T_ = ' '
AND D2_CLIENTE = Z02_CODIGO AND D2_LOJA = Z02_LOJA) ,

MERC = ( SELECT ISNULL ( SUM( CASE WHEN ( Z02_STATUS = 2) THEN Z02_VALOR ELSE 0 END ), 0 )
from Z02010 WHERE Z02_DATA BETWEEN '20130301' AND '20130331' AND Z02010.D_E_L_E_T_ = ' ' AND D2_CLIENTE = Z02_CODIGO AND D2_LOJA = Z02_LOJA ),

DF = ( SELECT ISNULL ( SUM( CASE WHEN ( Z02_STATUS = 3 ) THEN Z02_VALOR ELSE 0 END ), 0 )
from Z02010 WHERE Z02_DATA BETWEEN '20130301' AND '20130331' AND Z02010.D_E_L_E_T_ = ' ' AND D2_CLIENTE = Z02_CODIGO AND D2_LOJA = Z02_LOJA ),

RENTAB = (CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911') THEN SUM(D2_TOTAL) ELSE 0 END )
-
( SUM(B1_CUSTVDA*D2_QUANT)+ SUM(((CASE C5_TPFRETE WHEN 'C' THEN (D2_TOTAL*CC2_XPERFR)/100 ELSE 0 END)))
+ SUM(D2_VALIPI)
+ SUM(D2_VALICM)
+( SUM(CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911')
THEN ( CASE WHEN A3_COD IN ('NS','NS2','ALIS') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*0.0050
WHEN A3_COD IN ('KENJI','MG') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*B1_COMIS
ELSE (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*(B1_COMIS) END ) ELSE 0 END ))
+ CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911','5551','5556','5201','5905','5915','7949','6913','5949','6949','5102','6109')
THEN ( (SUM(D2_TOTAL)*0.0165) + (SUM(D2_TOTAL)*0.0760) + SUM(D2_TOTAL*0.031)) ELSE 0 END
+ SUM(D2_TOTAL*(A1_DESCDUP/100))),
PERC_RENTA= Replace(Convert(Decimal(12,6),(( CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911') THEN SUM(D2_TOTAL) ELSE 0 END -
( SUM(B1_CUSTVDA*D2_QUANT) +
SUM(D2_TOTAL*(A1_DESCDUP/100))+
SUM(((CASE C5_TPFRETE WHEN 'C' THEN (D2_TOTAL*CC2_XPERFR)/100 ELSE 0 END))) +
SUM(D2_VALIPI) +
SUM(D2_VALICM) +
SUM(D2_TOTAL*(A1_DESCDUP/100)) +
isnull(SUM(CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911')
THEN ( CASE WHEN A3_COD IN ('NS','NS2','ALIS') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*0.0050
WHEN A3_COD IN ('KENJI','MG') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*B1_COMIS
ELSE (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*(B1_COMIS) END
)
END ),0) +
CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911','5551','5556','5201','5905','5915','7949','6913','5949','6949','5102','6109') THEN ( (SUM(D2_TOTAL)*0.0760)+(SUM(D2_TOTAL)*0.0165)+SUM(D2_TOTAL*0.031)) ELSE 0 END
)) / SUM(D2_TOTAL) * 100 ) ),'.',',') ,

MCB = Replace(Convert(Decimal(12,6),( CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911') THEN SUM(D2_TOTAL) ELSE 0 END
-
( SUM(B1_CUSTPRI*D2_QUANT) + SUM(((CASE C5_TPFRETE WHEN 'C' THEN (D2_TOTAL*CC2_XPERFR)/100 ELSE 0 END)))
+ SUM(D2_VALIPI)
+ SUM(D2_VALICM)
+ (SUM(D2_TOTAL)*0.0165)
+ (SUM(D2_TOTAL)*0.0760)
+ CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911','6109') THEN SUM(D2_TOTAL*0.031) ELSE 0 END
+ SUM(D2_TOTAL*(A1_DESCDUP/100))
+ isnull((SUM(CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911') THEN ( CASE WHEN A3_COD IN ('NS','NS2','ALIS') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*0.0050
WHEN A3_COD IN ('KENJI','MG') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*B1_COMIS
ELSE (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*(B1_COMIS) END ) END )),0)
))),'.',','),
PERC_MCB = (( CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911') THEN SUM(D2_TOTAL) ELSE 0 END -
( SUM(B1_CUSTPRI*D2_QUANT) +
SUM(((CASE C5_TPFRETE WHEN 'C' THEN (D2_TOTAL*CC2_XPERFR)/100 ELSE 0 END)))
+ SUM(D2_VALIPI)
+ SUM(D2_VALICM)
+ (SUM(D2_TOTAL)*0.0165)
+ (SUM(D2_TOTAL)*0.0760)
+ CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911','6109') THEN SUM(D2_TOTAL*0.031) ELSE 0 END
+ SUM(D2_TOTAL*(A1_DESCDUP/100))
+ ISNULL(SUM(CASE WHEN D2_CF NOT IN ('5910','6910','5911','6911')
THEN ( CASE WHEN A3_COD IN ('NS','NS2','ALIS') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*0.0050
WHEN A3_COD IN ('KENJI','MG') THEN (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*B1_COMIS
ELSE (D2_TOTAL-(D2_TOTAL*(A1_DESCDUP/100)))*(B1_COMIS) END ) END ),0)
)) / SUM(D2_TOTAL )) * 100
FROM SD2010 SD2
INNER JOIN SA1010 SA1 ON A1_COD=D2_CLIENTE AND A1_LOJA=D2_LOJA AND SA1.D_E_L_E_T_=''
INNER JOIN SA3010 SA3 ON A3_COD=A1_VEND AND SA3.D_E_L_E_T_=''
INNER JOIN SB1010 SB1 ON B1_FILIAL=D2_FILIAL AND B1_COD=D2_COD AND B1_MSBLQL <> '1' AND SB1.D_E_L_E_T_=''
INNER JOIN CC2010 CC2 ON A1_COD_MUN=CC2_CODMUN AND CC2_EST = A1_EST AND CC2.D_E_L_E_T_=''
INNER JOIN SC5010 SC5 ON C5_FILIAL=D2_FILIAL AND C5_NUM=D2_PEDIDO AND SC5.D_E_L_E_T_=''
WHERE D2_TIPO = 'N'
AND D2_CLIENTE NOT IN ('002934','003285','006482')
AND D2_EMISSAO BETWEEN '20130301' AND '20130331' AND D2_COD BETWEEN '030000' AND '039999'
AND SD2.D_E_L_E_T_ = '' AND D2_CF IN ('5101','6101','5109','6109','5401','6401','5910','6910','5911','6911','6107')
GROUP BY D2_DOC,D2_SERIE,D2_EMISSAO,D2_COD,D2_FILIAL,D2_CLIENTE,D2_LOJA, A1_NOME,B1_CUSTVDA,CC2_XPERFR,B1_COMIS,B1_DESC, D2_CF, A3_COD, B1_CUSTPRI,C5_TPFRETE, A1_DESCDUP,A3_NOME,D2_EST,A1_SATIV1,A1_SATIV2
ORDER BY D2_FILIAL,D2_CLIENTE,D2_LOJA,D2_DOC,D2_SERIE

Obrigado !

Raul

tenta adicionar essa subquery na listagem dos seus inúmeros campos

(SELECT COUNT(D2_CLIENTE+D2_LOJA) FROM SD2010 WHERE D2_EMISSAO BETWEEN '20130301' AND '20130331' AND SD2010.D_E_L_E_T_ = ' ' AND D2_CLIENTE = SD2.D2_CLIENTE AND D2_LOJA = SD2.D2_LOJA) AS CONTADOR

Olá,

Obrigado pela ajuda !

Também achei esta solução : QTDE = COUNT(D2_CLIENTE+D2_LOJA) OVER(PARTITION BY D2_CLIENTE+D2_LOJA)

Deu certo assim, mas vou tentar a sua tambem, só acho que faltou colocar o SD2 após o FROM SD2010 não é ?

Valeu !

Raul.

Na verdade não, é que como é uma query interna usando a mesma tabela da externa, ou você adiciona um alias diferente, para poder fazer a relação D2_CLIENTE = D2_CLIENTE, ou deixa sem alias

No caso poderia ser

(SELECT COUNT(D2_CLIENTE+D2_LOJA) FROM SD2010 WHERE D2_EMISSAO BETWEEN '20130301' AND '20130331' AND SD2010.D_E_L_E_T_ = ' ' AND D2_CLIENTE = SD2.D2_CLIENTE AND D2_LOJA = SD2.D2_LOJA) AS CONTADOR

ou

(SELECT COUNT(D2_CLIENTE+D2_LOJA) FROM SD2010 SD21 WHERE SD21D2_EMISSAO BETWEEN '20130301' AND '20130331' AND SD21.D_E_L_E_T_ = ' ' AND SD21.D2_CLIENTE = SD2.D2_CLIENTE AND SD21D2_LOJA = SD2.D2_LOJA) AS CONTADOR

Abraço!