Professional Web Applications Themes

Problem with SUM and GROUP - MySQL

have a table with some like this fields : order, client_name, unit, type 1 - nobody - 1 - orange 2 - me - 2 - yellow 3 - another - 1 - black 1 - nobody - 1 - orange 1 - nobody - 1 - orange I want to make a select that return GROUP by "type" field but at same time the SUM or "unit". Ok, works fine, but now i want to "order" must be UNIQUE.. i mean, the SUM in this case return 6 instead 4 What can i do ? I use MySQL 4.1.18 ...

  1. #1

    Default Problem with SUM and GROUP

    have a table with some like this fields :

    order, client_name, unit, type

    1 - nobody - 1 - orange
    2 - me - 2 - yellow
    3 - another - 1 - black
    1 - nobody - 1 - orange
    1 - nobody - 1 - orange

    I want to make a select that return GROUP by "type" field but at same
    time the SUM or "unit". Ok, works fine, but now i want to "order" must
    be UNIQUE.. i mean, the SUM in this case return 6 instead 4

    What can i do ? I use MySQL 4.1.18

    The select that i use in this case is :

    select type, sum(unit) from table group by type

    The expected result is :

    orange, 1
    yellow, 1
    black, 1

    SuNcO Guest

  2. #2

    Default Re: Problem with SUM and GROUP

    "SuNcO" <com> wrote in message
    news:googlegroups.com... 

    How about:

    SELECT DISTINCT type, Sum(unit) FROM table GROUP BY type

    Or maybe you want Count instead of Sum:

    SELECT DISTINCT type, Count(unit) FROM table GROUP BY type





    Bosconian Guest

  3. #3

    Default Re: Problem with SUM and GROUP

    Well, in that case, distinct and group are the same. I want something
    like that but distinct order. I mean, that name_client is not repeated

    Is like if i group by type and order but if i group by order then the
    result is another


    Bosconian wrote: 
    >
    > How about:
    >
    > SELECT DISTINCT type, Sum(unit) FROM table GROUP BY type
    >
    > Or maybe you want Count instead of Sum:
    >
    > SELECT DISTINCT type, Count(unit) FROM table GROUP BY type[/ref]

    SuNcO Guest

  4. #4

    Default Re: Problem with SUM and GROUP

    It is difficult to figure out what you are asking for here.
    You are not including "order" in your output, and you converted the unit "2"
    on the "yellow" entry to a "1" on your desired output... why?

    So , according to what you seem to be asking for, with this sample data:
     
     
     

    You may get the following (with NON-reported order shown in (#) ):

    (3) black 1
    (5) black 1
    (8) black 1
    (1) orange 1
    (5) orange 1
    (6) orange 1
    (3) orange 1
    (9) orange 1
    (2) yellow 1
    (2) yellow 1
    (2) yellow 1

    But you would only see:

    black 1
    black 1
    black 1
    orange 1
    orange 1
    orange 1
    orange 1
    orange 1
    yellow 1
    yellow 1
    yellow 1

    The "1" does not seem to serve any purpose at all.

    So what exactly are you trying to create?
    It appears that you are trying to use or build an order or point-of-sale
    (POS) or inventory system.
    So the possible queries that may be useful are:
    - Sum of units for this order to be pulled for client.
    - Sum of units to be re-ordered and/or re-stocked.
    - A frequency chart to show "hot items".

    Either that or the "order" column represents a "priority order"?

    The point is, you haven't supplied enough information.

    HTH.

    ~ Duane Phillips.


    "SuNcO" <com> wrote in message
    news:googlegroups.com... 


    Duane Guest

Similar Threads

  1. Replies: 0
    Last Post: January 4th, 06:35 PM
  2. Group By problem
    By Dim in forum MySQL
    Replies: 14
    Last Post: March 20th, 09:53 PM
  3. Sum and Group problem with MSSQL
    By zu in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 2nd, 04:11 PM
  4. problem getting User Group Memebership in Active Directory
    By Patrick Olurotimi Ige in forum ASP.NET Security
    Replies: 2
    Last Post: May 10th, 12:49 AM
  5. group by problem on form
    By kderaedt in forum Microsoft Access
    Replies: 1
    Last Post: July 8th, 07:55 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