query with percentage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

query with percentage

Author

Topic

ArielR
Starting Member

21 Posts

Posted - 2008-03-18 : 18:10:10

SELECT dbo.Clientes.ZONA, SUM(dbo.Movimientos.Cantidad) AS Total, dbo.Movimientos.AnoMov
FROM dbo.Zonas INNER JOIN
dbo.Clientes ON dbo.Zonas.ZONA = dbo.Clientes.ZONA RIGHT OUTER JOIN
dbo.Movimientos ON dbo.Clientes.CLIENTE = dbo.Movimientos.CLIENTE

GROUP BY dbo.Clientes.ZONA, dbo.Movimientos.AnoMov, dbo.Zonas.DESCRIP

Result:

ZONA TOTAL ANOMOV
002 81079 2007
003 164330 2007
001 277345 2007
001 173603 2006
003 129105 2006
002 51154 2006

I need to add a column calculatting percentage TOTAL / SUM of TOTAL by ANOMOV

like this

ZON TOTAL ANOMOV %
002 81079 2007 15.51
003 164330 2007 41.44
001 277345 2007 53.05
001 173603 2006 49.07
003 129105 2006 36.48
002 51154 2006 14.46

Thank you.

jrogers
Starting Member

34 Posts

Posted - 2008-03-19 : 10:59:44

something like this should work


SELECT 
      dbo.Clientes.ZONA, 
      SUM(dbo.Movimientos.Cantidad)/sumbyyear.TotalByYear AS Total, 
      dbo.Movimientos.AnoMov
FROM dbo.Zonas INNER JOIN
     dbo.Clientes ON dbo.Zonas.ZONA = dbo.Clientes.ZONA 
RIGHT OUTER JOIN
     dbo.Movimientos ON dbo.Clientes.CLIENTE = dbo.Movimientos.CLIENTE
Right outer Join
     (
     Select 
           sum(dbo.Movimientos.Cantidad) as TotalByYear,
           dbo.Movimientos.CLIENTE,
           dbo.Movimientos.AnoMov
     From 
           dbo.Movimientos
     Group By
           dbo.Movimientos.CLIENTE,
           dbo.Movimientos.AnoMov
     ) as sumbyYear on dbo.Clientes.CLIENTE = sumbyyear.CLIENTE and
     dbo.Movimientos.AnoMov = sumbyyear.AnoMov
GROUP BY dbo.Clientes.ZONA, dbo.Movimientos.AnoMov, dbo.Zonas.DESCRIP

ArielR
Starting Member

21 Posts

Posted - 2008-03-19 : 13:39:16

I have this error: The column 'sumbyyear.TotalByYear' from the selection list is not valid, because it is not contained at the GROUP BY clause.
Something like this because my SQL is in spanish.
Thank you very much

ArielR
Starting Member

21 Posts

Posted - 2008-03-19 : 13:56:34

I Solver it that way thank to your colaboration jrogers:
SELECT TOP 100 PERCENT dbo.Clientes.ZONA, SUM(dbo.Movimientos.Cantidad) AS Total, SUM(dbo.Movimientos.Cantidad)
/ sumbyYear.TotalByYear * 100 AS Pje, dbo.Movimientos.AnoMov
FROM dbo.Zonas INNER JOIN
dbo.Clientes ON dbo.Zonas.ZONA = dbo.Clientes.ZONA RIGHT OUTER JOIN
dbo.Movimientos ON dbo.Clientes.CLIENTE = dbo.Movimientos.CLIENTE RIGHT OUTER JOIN
(SELECT SUM(dbo.Movimientos.Cantidad) AS TotalByYear, dbo.Movimientos.AnoMov
FROM dbo.Movimientos
GROUP BY dbo.Movimientos.AnoMov) sumbyYear ON dbo.Movimientos.AnoMov = sumbyYear.AnoMov
GROUP BY dbo.Clientes.ZONA, dbo.Movimientos.AnoMov, sumbyYear.TotalByYear
ORDER BY dbo.Movimientos.AnoMov

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-19 : 14:22:50

You haven't provided table structures or sample data, but alter this line
SUM(dbo.Movimientos.Cantidad)/sumbyyear.TotalByYear AS Total
TO
dbo.Movimientos.Cantidad*100.0/sumbyyear.TotalByYear AS Total

since you have already summed it up. Also th reason I mutiplied by 100.0 is because
in SQL Server an integer divided by an integer is an integer. So 1/2 = 0, not .5

Jim

Jim

ArielR
Starting Member

21 Posts

Posted - 2008-03-20 : 07:04:31

Thank you very much Jim. I'am beginner in SQL. Your knowledge like other in the forum help me very much. Thanks again...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources