SQL - count and percent

hit a wall on getting a count AND a percentage of total in a single query

I want the TOTAL units per location AND the PERCENT of TOTAL ACROSS ALL LOCATIONS...

what I've got - is returning a 0 (zero) for percentage - I believe I have to cast the figure into a decimal or real type - but whatever i try (casting each element, or casting t开发者_C百科he whole result - i get errors...)

SELECT 
count(I.ID) as count
,L.ID
,(
    count(I.ID) 
    / 
    ( SELECT count(I2.ID) 
      FROM LOCATION L2
      JOIN ITEM I2 ON I2.LocID = L2.ID
      WHERE L2.ID IN (36,38,39,40) )
) AS percent
FROM LOCATION L
JOIN ITEM I ON I.LocID = L.ID
WHERE L.ID IN (36,38,39,40)
GROUP BY L.ID

Any thoughts on how to return a decimal??? or just make it more elegant .. period?

What I'd "expect"

ID      COUNT   PERCENT
2436    362     30.47
2438    184     15.48
2439    173     14.56
2440    172     14.47
2441    151     12.71
2442    54      4.54
2702    92      7.74

======== "t" suggested this - count(I.ID) * 100.0 / count(*) over () AS percent What is the "over()" supposed to do??

here's the return:

ID      COUNT   PERCENT
2436    362     51
2438    184     26
2439    173     24
2440    172     24
2441    151     21
2442    54      7
2702    92      13

========== SOLUTION

SELECT 
   count(I.ID) as count
   ,L.ID
   ,(
    1.0 * count(I.ID) / (
      SELECT count(I2.ID) 
      FROM LOCATION L2
      JOIN ITEM I2 ON I2.LocID = L2.ID
      WHERE L2.ID = L1.ID )
   ) * 100  AS percent
   FROM LOCATION L
   JOIN ITEM I ON I.LocID = L.ID
WHERE L.ID IN (36,38,39,40)
GROUP BY L.ID

Simply multiply by a float/decimal value

SELECT 
   count(I.ID) as count
   ,L.ID
   ,(
    100.0 * count(I.ID) 
    / 
     (
      SELECT count(I2.ID) 
      FROM LOCATION L2
      JOIN ITEM I2 ON I2.LocID = L2.ID
      WHERE L2.ID = L1.ID
     )
   ) AS percent
   FROM LOCATION L
   JOIN ITEM I ON I.LocID = L.ID

 WHERE L.ID IN (36,38,39,40)
 GROUP BY L.ID

Also, you'll need to consider a zero divisor giving "divide by zero" errors

First, check if your query works and returns the right values with:

SELECT 
count(I.ID) as count
,L.ID
,(SELECT count(I2.ID) 
      FROM LOCATION L2
      JOIN ITEM I2 ON I2.LocID = L2.ID
      WHERE L2.ID IN (36,38,39,40)
)
FROM LOCATION L
JOIN ITEM I ON I.LocID = L.ID
WHERE L.ID IN (36,38,39,40)
GROUP BY L.ID

Then use the proper syntax for DB2 casting:

CAST(%Expression% AS data-type)

You could use the ROUND() function...

ROUND(..., 2) AS percent

Or just multiply by a decimal value (i.e. 1.0). You also might want to include an IF or CASE (depending on your SQL server) to get around possible zero values