Home / Questions / 165648
Accepted
Adrian Hernando Solanas
Asked: 2020-05-19 07:32:21 +0800 CST 2020-05-19 07:32:21 +0800 CST

SQL, GROUP BY problem

  • 772

I have the following problem when sorting some records:

enter image description here

As you can see in the image, I am trying to filter some sales and I get duplicate fields: for example the tapas...

It should be sorted by NomFam "Family name".

Changing the order of the GROUP BY:

enter image description here

The thing is that if I don't put those GROUP BY I get a grouping error...

Greetings and thank you very much!

sql

4 Answers

  1. Best Answer

    The problem is that you are grouping ImporteBruto, and that you are confused with how to get the percentage. If I understand correctly, the percentage is based on the total gross amount of your filtered data, which is a different figure from the one you are calculating.

    A quick way to get what you're looking for would be to use the following query (assuming SQL Server 2005+):

    SELECT  CodFam,
        NomFam,
        SUM(Unidades) Cantidad,
        SUM(ImporteBruto) Total,
        SUM(ImporteBruto) / SUM(ImporteBruto) OVER() Porcentaje
FROM dbo.PI314_Detalle_Facturas
WHERE codlocal = 12
AND FechaContable = '17/05/2018' -- esta columna es datetime/date?, en ese caso usa '20180517'
AND ImporteBruto <> 0
GROUP BY CodFam,
         NomFam
;
    • 2

  2. Ordering and grouping are independent... You have to add a clause order byto order the way you want:

    select ...
from ...
where ...
group by NomFam, CodFam, ImporteBruto
order by NomFam, CodFam, ImporteBruto
    • 1

  3. I think you ask for this:

     DECLARE @total decimal(15,5) = (SELECT SUM(ImporteBruto) from PI314_Detalle_Facturas)
 SELECT  CodFam,
    NomFam,
    SUM(Unidades) Cantidad,
    SUM(ImporteBruto) Total,
    SUM(ImporteBruto) * 100 / @total Porcentaje
 FROM dbo.PI314_Detalle_Facturas
 WHERE codlocal = 12
 AND FechaContable = '17/05/2018' 
 AND ImporteBruto <> 0
 GROUP BY CodFam,
     NomFam
 ORDER BY NomFam, CodFam
    • 1

  4. Add order by NomFam and if you want to order it by other fields add order by NomFam,CodFam,ImporteBruto

    • 0