I need to create a report that sums up values by category and groups this information according to origin

Asked

Viewed 90 times

1

I need to create a report that sums up values by category and groups that information according to origin. I explain: I need to bring the sum of monthly fees, enrollment, material and fees, but grouping this information by Class category. Classes differ according to initial letters (TB%, CT%, TE%...). I was able to sum up the figures, but I was unable to group by class category (bring these figures to all classes starting with TB% in one line, with CT% in another, etc).

SELECT
    UNIDADE, TURMA,
    SUM( CASE   WHEN CODTX IN ('000','001','002') THEN VALORPAGO ELSE 0 END) as "MENSALIDADE",
    SUM( CASE   WHEN CODTX IN ('003','004') THEN VALORPAGO ELSE 0 END) as "MATRÍCULA",
    SUM( CASE   WHEN CODTX IN ('005','006', '007') THEN VALORPAGO ELSE 0 END) as "MATERIAL",
    SUM( CASE   WHEN CODTX NOT IN ('000','001','002','003','004','005','006','007') THEN VALORPAG ELSE 0 END) as "TAXAS",
    ROUND(SUM(VALORPAGO),0) as "TOTAL"
FROM
    DEBITOS
WHERE
    UNIDADE = '008'
    AND DATAPAGO BETWEEN '2021-05-01 00:00:00.000' AND '2021-05-31 00:00:00.000'
    AND VALORPAGO IS NOT NULL 
GROUP BY
    UNIDADE, TURMA

Resultado do select

sql database sql-server

  • How do you need data output ? The category , in thesis , can be assembled through the FUNCTION SUBSTRING but at least for me it was little the required form of the output.

  • Ah! Yes. I need the total values of each column: In the CLASS column all TB% classes grouped in a single row category Class B and I need to sum all the Tuition values of these classes and display the total, in the same way for Registration, Material, etc). All TC% classes grouped in a single row category Class C. I don’t know if I was able to clarify...

  • Do you need the sums per class and class category or only per class category? If it’s only per class, try changing where you are Turma for Left(Turma, 2)

  • I need it by class category (to add the values of all those that start with TB%, then all the values of those that start with CT%. This for Tuition, Enrollment etc). ;)

  • Try doing some tests the way I suggested earlier, changing Turma for Left(Turma, 2) in Select and Group By

  • It worked, imex. I just couldn’t leave with the name I need yet. But the summation problem has been solved. Muitooo thanks!

Show 1 more comment

2 answers

1

try with CUBE or ROLLUP , generates a "running total" I had no way to test however.

SELECT
    UNIDADE, 
    TURMA,
    SUBSTRING(TURMA,1,2) GRUPO,
    SUM( CASE   WHEN CODTX IN ('000','001','002') THEN VALORPAGO ELSE 0 END) as "MENSALIDADE",
    SUM( CASE   WHEN CODTX IN ('003','004') THEN VALORPAGO ELSE 0 END) as "MATRÍCULA",
    SUM( CASE   WHEN CODTX IN ('005','006', '007') THEN VALORPAGO ELSE 0 END) as "MATERIAL",
    SUM( CASE   WHEN CODTX NOT IN ('000','001','002','003','004','005','006','007') THEN VALORPAG ELSE 0 END) as "TAXAS",
    ROUND(SUM(VALORPAGO),0) as "TOTAL"
FROM
    DEBITOS
WHERE
    UNIDADE = '008'
    AND DATAPAGO BETWEEN '2021-05-01 00:00:00.000' AND '2021-05-31 00:00:00.000'
    AND VALORPAGO IS NOT NULL 
GROUP BY CUBE (UNIDADE, 
               TURMA,
               SUBSTRING(TURMA,1,2))
ORDER BY 1,2,3

  • I tried to take the test here, but my bank wouldn’t let me. I will need to change the level of compatibility of the database and I am waiting for authorization for it. As soon as I test I inform here. Thank you very much.

  • Strange , believed to be standard, but ok.

0

Try using the WITH command

Thus:

WITH RELATORIO AS (SELECT
    UNIDADE, TURMA,
    SUM( CASE   WHEN CODTX IN ('000','001','002') THEN VALORPAGO ELSE 0 END) as 'MENSALIDADE',
    SUM( CASE   WHEN CODTX IN ('003','004') THEN VALORPAGO ELSE 0 END) as 'MATRICULA',
    SUM( CASE   WHEN CODTX IN ('005','006', '007') THEN VALORPAGO ELSE 0 END) as 'MATERIAL',
    SUM( CASE   WHEN CODTX NOT IN ('000','001','002','003','004','005','006','007') THEN VALORPAG ELSE 0 END) as 'TAXAS',
    ROUND(SUM(VALORPAGO),0) as 'TOTAL'
FROM
    DEBITOS
WHERE
    UNIDADE = '008'
    AND DATAPAGO BETWEEN '2021-05-01 00:00:00.000' AND '2021-05-31 00:00:00.000'
    AND VALORPAGO IS NOT NULL 
GROUP BY
    UNIDADE, TURMA )

SELECT TURMA,
SUM(MENSALIDADE), SUM(MATRICULA), SUM(MATERIAL), SUM(TAXAS), SUM(TOTAL)
FROM RELATORIO
GROUP BY TURMA

Browser other questions tagged sql database sql-server

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