mysql query to get percentage

Discussion in 'MySQL' started by himurak, Dec 8, 2009.

0
  1. #1
    Hi,

    I have the following table in the customer database:

    customertb

    id zip in
    1 111 1
    2 222 1
    3 333 1
    4 111 1
    5 444
    6 555
    7 111
    8 333 1


    I am trying to get the following results:

    Zip Total In Total Zip Percentage
    111 2 3 66.66666667
    222 1 1 100
    333 2 2 100
    444 0 1 0
    555 0 1 0

    All the zips are listed even when there's no customer in.

    Can you please point me in the right direction? I have been working at this for a week.

    Thanks
     
    himurak, Dec 8, 2009 IP
  2. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #2
    first of all, never keep a numeric column as blank..

    following update query will mark all values for in column as 0 which are blank

    UPDATE customertb SET in = 0 WHERE LENGTH(TRIM(in)) <= 0

    Then use following query to get the desired output..

    SELECT Zip, COUNT(*) AS Total, SUM(in) AS TotalIn, SUM(in)*100/COUNT(*) AS Percent FROM customerdb GROUP BY Zip
     
    mastermunj, Dec 8, 2009 IP
  3. himurak

    himurak Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thank you so much for the solution.
    It worked perfectly.
     
    himurak, Dec 8, 2009 IP