Professional Web Applications Themes

sum of sums of columns - MySQL

Hi all, I'va many columns in wich I've some amounts. I need to get the total for every client with also the total of totals... my table. idclient mediumint unsigned not null, amount1 float(10,2) unsigned, amount2 float(10,2) unsigned, amount3 float(10,2) unsigned; now I do this: select sum(amount1) as total1, sum(amount2) as total2, sum(amount3) as total3 from amounts group by idclient. and I need also this: sum(amount1+amount2+amount3) as total How can I do in MySQL ? Thanks for help. Bob...

  1. #1

    Default sum of sums of columns

    Hi all,

    I'va many columns in wich I've some amounts. I need to get the total for
    every client with also the total of totals...

    my table.

    idclient mediumint unsigned not null,
    amount1 float(10,2) unsigned,
    amount2 float(10,2) unsigned,
    amount3 float(10,2) unsigned;

    now I do this: select sum(amount1) as total1, sum(amount2) as total2,
    sum(amount3) as total3 from amounts group by idclient.

    and I need also this: sum(amount1+amount2+amount3) as total

    How can I do in MySQL ?

    Thanks for help.

    Bob



    Bob Guest

  2. #2

    Default Re: sum of sums of columns

    > I'va many columns in wich I've some amounts. I need to get the total for 
    This does actually work but if you have one value = null then the result is
    null...that's why it didn't work since the beginning for me.

    Cheers



    Bob Guest

  3. #3

    Default Re: sum of sums of columns


    "Bob Bedford" <com> schreef in bericht
    news:45f59403$0$3817$sunrise.ch... 
    > This does actually work but if you have one value = null then the result
    > is null...that's why it didn't work since the beginning for me.
    >
    > Cheers
    >
    >[/ref]

    select sum(amount1) as total1, sum(amount2) as total2, sum(amount3) as
    total3 , sum(IFNULL(amount1,0)+IFFNULL(amount2,0)+IFNULL(am ount3,0)) as
    total4 from amounts group by idclient.


    see: http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html




    Luuk Guest

Similar Threads

  1. sums and groups
    By dcm in forum Coldfusion Database Access
    Replies: 1
    Last Post: November 10th, 07:42 PM
  2. SQL/Coldfusion SUMS
    By Simonbullen in forum Macromedia ColdFusion
    Replies: 3
    Last Post: August 31st, 05:29 PM
  3. Columns and Inherited Datagrid...Active Schema does not support columns
    By rob thomson in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 4th, 03:09 PM
  4. UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE
    By Jon Spartan in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 9th, 04:48 PM
  5. Replies: 0
    Last Post: July 2nd, 06:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139