Post reply

Percentage calculation

  • November 3, 2011 at 4:13 am

    #259529

    Hi All

    Maybe its me being stupid here and I can't see the clear mistake in the code but my percentage calculation isn't working

    DECLARE @NumClick BIGINT

    SELECT

    @NumClick = COUNT(*)

    FROM

    table1

    WHERE

    col1 between '2011-10-01' and '2011-11-01'

    SELECT @NumClick --Returns a value of 1368003

    SELECT

    COUNT(SC.col1) AS NumberOfClicks,

    CM.col2 AS Manufacturer,

    CR.col2 AS Model,

    @NumClick,

    ((COUNT(SC.col1) / @NumClick) * 100) AS PercentageOfClicks

    FROM

    dbo.table1 SC

    LEFT OUTER JOIN

    dbo.table2 CM

    ON

    SC.col1 = CM.col1

    LEFT OUTER JOIN

    dbo.table3 CR

    ON

    SC.col1 = CR.col1

    WHERE

    SC.col10 BETWEEN '2011-10-01' AND '2011-11-01'

    AND

    SC.col2 IS NOT NULL

    AND

    SC.col3 IS NOT NULL

    GROUP BY

    CM.col2,

    CR.col2

    ORDER BY

    1 DESC

    The results are

    1368003 for @NumClick

    Selection of the rows

    59459BMW 3 SERIES 13680030

    47776VOLKSWAGEN GOLF 13680030

    45197FORD FOCUS 13680030

    Now I would of thought that

    ((59459 / 1368003) * 100) = 4.34 but I keep getting 0

    I have done a convert around the percentage calculation to Numeric(18,2) and Decimal(18,2) but no luck.

    Any ideas

  • November 3, 2011 at 4:19 am

    #1402560

    you need to use decimals not INT's . Integers are rounded up to a whole number

  • November 3, 2011 at 4:25 am

    #1402563

    Thanks Edward, totally appreciate that, I would of still expected SQL to come back with 4 and not 4.34

  • November 3, 2011 at 4:25 am

    #1402564

    i think u need to convert SC.col1 as decimal. it might be an integer column

  • November 3, 2011 at 4:26 am

    #1402565

    the columns where converted into NUMERIC and DECIMAL using CONVERT, please see my first post

    converting them to FLOAT has solved the issue

  • November 3, 2011 at 4:31 am

    #1402566

    SELECT ((59459 / 1368003) * 100), --this is 0

    ((59459 / 1368003.0) * 100) --this is 4.346408500

    If you need further prodding, supply DDL please.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 3, 2011 at 4:47 am

    #1402568

    anthony.green (11/3/2011)

    Thanks Edward, totally appreciate that, I would of still expected SQL to come back with 4 and not 4.34

    Those are expected results when working with INT values. The / operation is performed first, which means 59459 / 1368003 is calculated at 0.043464085, however this is rounded to 0 before the multiplication is performed giving you a final answer of 0.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • November 3, 2011 at 5:10 am

    #1402575

    Doh, of course, I knew it would of been something simple I was missing.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply