SQL - Count Distinct

Asked

Viewed 112 times

0

I am trying to make the relationship of two tables: one of Customers and one of Refills.

I have already created reload tracks, total amount of transactions, type of transaction (Reload or Chargeback), and inform the period I want to extract... However, I need to identify the number of different clients I have in this result:

    SELECT C.PERSON_TYPE AS TIPO_PESSOA_PF_PJ,
        COUNT(DISTINCT AC.ID) Nro_Of_Dist,
        date_format(TH.IMPUTATION_DATE,'yyyyMM') AS DATA_DE_IMPUTE_CYBERO,
        date_format(TE.EVENT_DATE,'yyyyMM') AS DATA_DO_EVENTO,
        PT.SHORT_DESC AS FORMA_PAGAMENTO,
        AC.NEMOTECNICO AS ATIVIDADE,
        SUM(TH.AMOUNT) AS VALOR,
        COUNT(TH.AMOUNT) as Qtde,
       IF(TH.AMOUNT <=50,"Ate 50",if(TH.AMOUNT <=75,"De 50 a 75",if(TH.AMOUNT <=100,"De 75 a 100",if(TH.AMOUNT <=150,"De 100 a 150",if(TH.AMOUNT <=200,"De 150 a 200",if(TH.AMOUNT <=500,"De 200 a 500",if(TH.AMOUNT <=1000,"De 500 a 1000",if(TH.AMOUNT <=2000,"De 1000 a 2000",if(TH.AMOUNT <=5000,"De 2000 a 5000","Acima de 5000"))))))))) as FaixaRecarga,
        SP.LONG_DESC AS PLANO,
        TE.COMMENTS3 AS CANAL_FORMA_DE_PAGAMENTO
FROM Veloe.ACTIVITY AC INNER JOIN Veloe.TRANSACTION_REASON_ACTIVITY TA ON ( AC.ID = TA.ACTIVITY )
                                INNER JOIN Veloe.TXN_HIST TH ON ( TA.TRANSACTION_REASON = TH.TRANSACTION_REASON )
                                INNER JOIN Veloe.ACCOUNT A ON ( TH.ACCOUNT = A.ID )
                                INNER JOIN Veloe.CUSTOMER_OPERATION CO ON ( A.SUBPRODUCT = CO.SUBPRODUCT AND A.BRANCH = CO.BRANCH AND A.CURRENCY = CO.CURRENCY AND A.OPERATION_ID = CO.OPERATION_ID )
                                INNER JOIN Veloe.CUSTOMER_OPERATION_TYPE TP ON ( CO.TYPE = TP.ID )
                                INNER JOIN Veloe.CUSTOMER C ON ( CO.CUSTOMER = C.ID )
                                LEFT JOIN Veloe.ADDRESS B ON ( CO.CUSTOMER = B.CUSTOMER )
                                --LEFT JOIN Veloe.LOCALITY D ON ( B.LOCALITY_ZIP_CODE = D.ID )
                                LEFT JOIN Veloe.PROVINCE_CODE E ON ( B.PROVINCE_CODE = E.ID )
                                LEFT JOIN Veloe.PAYMENT_TYPE PT ON ( A.CNT_STOP_PAYMENTS_YTD = PT.ID )
                                INNER JOIN Veloe.SUBPRODUCT SP ON ( A.SUBPRODUCT = SP.ID )
                                INNER JOIN Veloe.TXN_HIST_EXT TE ON ( TH.ID = TE.TXN_HIST )
WHERE AC.NEMOTECNICO IN ('RECARGA_MANUAL','RECARGA_AUTOMATICA','ESTORNO_RECARGA_AUTO','ESTORNO_RECARGA_MANUAL') -- --- AQUI PRECISA INFORMAR TODAS AS ACTIVITY DE RECARGAS
AND TP.NEMOTECNICO = 'FIRST_HOLDER_PR' 
AND TE.EVENT_DATE >=  '2020-01-01 00:00:00:0' AND TE.EVENT_DATE <=  '2020-06-30 00:00:00:0'
AND B.IS_FOR_CORRESPONDENCE = 1

But when I call COUNT(DISTINCT AC.ID) Nro_Of_Dist, it brings me the distinct amount of customers in the customer table and not in the top up table.

sql dbeaver

  • If you are using aggregation functions (SUM, COUNT) you should not have the GROUP BY clause?

  • Yes, without the group by this consultation nor should it work...with Group by is to solve the problem. By grouping by fields that are not in an aggregation function (SUM, COUNT, AVG, etc.), it will already filter for you and leave only distinct records.

  • What is the recharge table?

1 answer

-1

Hello,

To perform the necessary calculations you need to add a GROUP BY according to the selected criteria

Browser other questions tagged sql dbeaver

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