I have the following problem when ordering some records:
As you can see in the image, I'm trying to filter some sales and I get duplicate fields: for example the covers ...
It should be sorted by NomFam "Family name".
Changing the GROUP BY order:
The fact is that if I do not put those GROUP BY I get a grouping error ...
Greetings and thank you very much!
The problem is that you are grouping ImporteBruto , and that you are confused with the way to get the percentage. If I understand correctly, the percentage is about the total gross amount of your filtered data, which is a different data than the one you are calculating.
A quick way to get what you're looking for would be using 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
;
The order and the grouping are independent ... You have to add a clause order by to order the way you want:
select ...
from ...
where ...
group by NomFam, CodFam, ImporteBruto
order by NomFam, CodFam, ImporteBruto
I think you ask 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
Add order by NomFam and if you want to order it by other fields add order by NomFam, CodFam, AmountBruto