Professional Web Applications Themes

how many of a kind - MySQL

Hi, I'm a newbie, so please be patient. I've a table product_name | cat_id prod1 | 1 prod2 | 2 prod3 | 2 prod4 | 2 prod5 | 3 prod6 | 3 prod7 | 4 prod8 | 5 prod9 | 5 prodN | N Is there any way to know how many 1s, 2s, Ns, etc. do we have in the cat_id field or is this a middleware task? I'm not talking about the DISTINCT: I want to know for every number in cat_id how many products do we have. Thanks!...

Sponsored Links
  1. #1

    Default how many of a kind


    Hi, I'm a newbie,
    so please be patient.

    I've a table

    product_name | cat_id

    prod1 | 1
    prod2 | 2
    prod3 | 2
    prod4 | 2
    prod5 | 3
    prod6 | 3
    prod7 | 4
    prod8 | 5
    prod9 | 5
    prodN | N

    Is there any way to know how many 1s, 2s, Ns, etc. do we have in the
    cat_id field or is this a middleware task? I'm not talking about the
    DISTINCT: I want to know for every number in cat_id how many products do
    we have.

    Thanks!
    Sponsored Links
    Stefano Guest

  2. #2

    Default Re: how many of a kind

    On May 9, 5:29 pm, Stefano Perna <it> wrote: 

    select count(product_name) from table group by cat_id

    strawberry Guest

  3. #3

    Default Re: how many of a kind

    strawberry wrote: 



    very good, thanks!

    Is the result always ordered? That is, do I receive data as they were
    ordered by cat_id?
    Stefano Guest

  4. #4

    Default Re: how many of a kind

    On May 9, 6:42 pm, Stefano Perna <it> wrote: 
    >
    > very good, thanks!
    >
    > Is the result always ordered? That is, do I receive data as they were
    > ordered by cat_id?[/ref]

    Like me, Mysql is inherently lazy. You can pretty much assume that it
    doesn't do anything unless specifically asked - but sometimes you get
    lucky.

    strawberry Guest

  5. #5

    Default Re: how many of a kind

    strawberry ha scritto:
     



    uhm... this makes me think this apparently useful idea is useless...
    That is: I need a precise sequence. In practice, I've two tables, one
    containing categories

    categories

    cat_id | cat_type
    1 | Washers
    2 | Dryers
    3 | Phones
    4 | Radios

    the other one is the one I posted in a previous post

    products

    product_name | cat_id

    prod1 | 1
    prod2 | 2
    prod3 | 2
    prod4 | 2
    prod5 | 3
    prod6 | 3
    prod7 | 4

    Using PHP, I must serve to the client a menu like this one:

    Washers(3)
    Dryers(5)
    Phones(2)
    Radios(1)

    (there's something similar on E-Bay)

    So, this is my goal:
    is there something MySQL can do for me or I must (over)use PHP with
    multiple queries?

    Thanks!!


    Stefano Guest

  6. #6

    Default Re: how many of a kind

    Stefano Perna wrote: 
    >
    >
    >
    > uhm... this makes me think this apparently useful idea is useless...
    > That is: I need a precise sequence. In practice, I've two tables, one
    > containing categories
    >
    > categories
    >
    > cat_id | cat_type
    > 1 | Washers
    > 2 | Dryers
    > 3 | Phones
    > 4 | Radios
    >
    > the other one is the one I posted in a previous post
    >
    > products
    >
    > product_name | cat_id
    >
    > prod1 | 1
    > prod2 | 2
    > prod3 | 2
    > prod4 | 2
    > prod5 | 3
    > prod6 | 3
    > prod7 | 4
    >
    > Using PHP, I must serve to the client a menu like this one:
    >
    > Washers(3)
    > Dryers(5)
    > Phones(2)
    > Radios(1)
    >
    > (there's something similar on E-Bay)
    >
    > So, this is my goal:
    > is there something MySQL can do for me or I must (over)use PHP with
    > multiple queries?
    >
    > Thanks!!
    >
    >[/ref]
    as strawberry would tell you: if you add and "order by" clause at the
    end of your statement, it'll order it for you. for example:

    select blah blah
    from blah
    group by bla
    order by cat_id

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  7. #7

    Default Re: how many of a kind

    lark 
    > as strawberry would tell you: if you add and "order by" clause at the
    > end of your statement, it'll order it for you. for example:
    >
    > select blah blah
    > from blah
    > group by bla
    > order by cat_id
    >[/ref]



    oh, thanks very much:
    being a newbie, I could not understand it very easily.

    Thanks for pointing that out!
    Stefano Guest

  8. #8

    Default Re: how many of a kind


    one further question:

    in the categories table, I've an "is_active" field.
    What if I want to SELECT only where is_active=1?

    How should be changed this?

    SELECT COUNT(product_name) FROM products GROUP BY cat_id ORDER BY cat_id

    Is it possible? Thanks!
    Stefano Guest

  9. #9

    Default Re: how many of a kind

    On Thu, 10 May 2007 08:21:09 +0200, Stefano Perna <it>
    wrote:
     

    Ask MySQL exactly how you asked us :

    SELECT COUNT(product_name)
    FROM products

    where is_active=1

    GROUP BY cat_id ORDER BY cat_id
    subtenante Guest

  10. #10

    Default Re: how many of a kind

    subtenante ha scritto:
     



    one moment:
    is_active is in the categories table (not in products)!
    Stefano Guest

  11. #11

    Default Re: how many of a kind

    Stefano Perna <it> wrote in
    <4642c037$0$17951$news.tin.it>: 
    >
    > one moment:
    > is_active is in the categories table (not in products)![/ref]

    Since you don't seem to know SQL at all, I would recommend
    getting some sort of introductory book and reading it
    first. Meanwhile hope your team leader doesn't find out
    you're not up to snuff.

    mysql> select * from categories;
    +----+----------+-----------+
    | id | cat_type | is_active |
    +----+----------+-----------+
    | 1 | Washers | 1 |
    | 2 | Dryers | 1 |
    | 3 | Phones | 1 |
    | 4 | Radios | 1 |
    | 5 | Gizmos | 0 |
    +----+----------+-----------+
    5 rows in set (0.00 sec)

    mysql> select * from products;
    +----+--------------+--------+
    | id | product_name | cat_id |
    +----+--------------+--------+
    | 1 | prod1 | 1 |
    | 2 | prod2 | 2 |
    | 3 | prod3 | 2 |
    | 4 | prod4 | 3 |
    | 5 | prod5 | 3 |
    +----+--------------+--------+
    5 rows in set (0.00 sec)

    mysql> select c.cat_type,count(p.product_name) from
    categories as c left join products as p on (c.id=p.cat_id)
    where c.is_active=1 group by c.id order by c.id;
    +----------+-----------------------+
    | cat_type | count(p.product_name) |
    +----------+-----------------------+
    | Washers | 1 |
    | Dryers | 2 |
    | Phones | 2 |
    | Radios | 0 |
    +----------+-----------------------+
    4 rows in set (0.00 sec)

    --
    Pavel Lepin
    Pavel Guest

  12. #12

    Default Re: how many of a kind

     



    Hi, and thanks for your reply. I generally work with client-side
    technologies and middleware and the team leader perfectly knows it (!),
    this time has been made an exception. In any case, I think you should
    learn a little more about good manners.
    Stefano Guest

  13. #13

    Default Re: how many of a kind

    On May 10, 9:04 am, Stefano Perna <it> wrote: 
    >
    > Hi, and thanks for your reply. I generally work with client-side
    > technologies and middleware and the team leader perfectly knows it (!),
    > this time has been made an exception. In any case, I think you should
    > learn a little more about good manners.[/ref]

    On May 10, 9:04 am, Stefano Perna <it> wrote: 
    >
    > Hi, and thanks for your reply. I generally work with client-side
    > technologies and middleware and the team leader perfectly knows it (!),
    > this time has been made an exception. In any case, I think you should
    > learn a little more about good manners.[/ref]

    Hmm, good manners could include reading the manual.

    strawberry Guest

  14. #14

    Default Re: how many of a kind

    strawberry ha scritto:

     



    well, that's true, but I'm not sure reading the manual would help so
    much if the resulting query is the (complicated) third one! And, anyway,
    if one asks is because he doesn't know or he has not found what he was
    lookin for...
    Stefano Guest

  15. #15

    Default Re: how many of a kind

    Stefano Perna <it> wrote in
    <4642d209$0$4794$news.tin.it>: 
    >
    > In any case, I think you should learn a little
    > more about good manners.[/ref]

    Dear Sir,

    How kind it is of you to point out my awfully bad manners to
    me right after receiving some free advice (which is,
    undoubtedly, no more than your just due, since Usenet *is*
    a free helpdesk after all) on something you are supposedly
    being paid for. Please accept my deepest apologies for
    offending your marvellously refined sensibilities, and let
    me assure Your Lordship that This Will Not Happen Again.

    I remain your humble and most obedient servant--
    (indecipherable)

    Okay, just kidding, but you already guessed that, didn't
    you?
     

    I don't know what kind of team management is practised in
    your organisation, but being a leader of a small team
    myself, I know that when I assign a task to someone I
    expect that either:

    1. They're perfectly capable of dealing with the task.

    2. They're not capable of dealing with the task at the
    moment, but perfectly capable of buying a book/reading a
    tutorial, then practising the necessary skills on their own
    until they become capable of dealing with the task at hand
    under light supervision of someone conversant with that
    same set of skills (which is usually me since it's a very
    small team).

    3. They're not capable of dealing with the task on their
    own, but perfectly capable of doing it under mentoring of
    someone conversant with the required skills (which is
    usually me since it's a very small team).

    These three are the more or less workable options in my
    experience.

    4. They're not capable of dealing with the assigned task, so
    instead of getting off their and working on it they ask
    someone else to do it for them, please, pretty please?

    This one is not a workable option, on the other hand. In
    fact, if one of my colleagues were doing that, I'd rip him
    a new one, then recommend to either fire him or transfer to
    a pure management position, where getting others to work
    for free is a priceless skill.

    --
    Pavel Lepin
    Pavel Guest

  16. #16

    Default Re: how many of a kind

    Stefano Perna wrote: 
    >
    >
    >
    > well, that's true, but I'm not sure reading the manual would help so
    > much if the resulting query is the (complicated) third one! And, anyway,
    > if one asks is because he doesn't know or he has not found what he was
    > lookin for...[/ref]

    Sorry, I agree with the others. You didn't even try - you just came
    running here to get us to do your work for you. Do we get your
    paycheck, also?

    Most of us here are willing to help those who are trying. But if you
    don't even try yourself soon you'll get no answers here.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  17. #17

    Default Re: how many of a kind

    Pavel Lepin schrieb: 
    >> In any case, I think you should learn a little
    >> more about good manners.[/ref]
    >
    > Dear Sir,
    >
    > How kind it is of you to point out my awfully bad manners to
    > me right after receiving some free advice[/ref]

    Communication in a newsgroup isn't broadband enough to give you enough
    information to judge the other person, yet you insist that you can judge
    him anyway. Inisisting on gratitude that came over as rudeness adds on
    top of that.
    I don't know the technical term for this kind of attitude, but it's most
    certainly not "good manners".

    Regards,
    Jo
    Joachim Guest

  18. #18

    Default Re: how many of a kind

    Pavel Lepin wrote: 
    >> In any case, I think you should learn a little
    >> more about good manners.[/ref]
    >
    > Dear Sir,
    >
    > How kind it is of you to point out my awfully bad manners to
    > me right after receiving some free advice (which is,
    > undoubtedly, no more than your just due, since Usenet *is*
    > a free helpdesk after all) on something you are supposedly
    > being paid for. Please accept my deepest apologies for
    > offending your marvellously refined sensibilities, and let
    > me assure Your Lordship that This Will Not Happen Again.
    >
    > I remain your humble and most obedient servant--
    > (indecipherable)
    >
    > Okay, just kidding, but you already guessed that, didn't
    > you?

    >
    > I don't know what kind of team management is practised in
    > your organisation, but being a leader of a small team
    > myself, I know that when I assign a task to someone I
    > expect that either:
    >
    > 1. They're perfectly capable of dealing with the task.
    >
    > 2. They're not capable of dealing with the task at the
    > moment, but perfectly capable of buying a book/reading a
    > tutorial, then practising the necessary skills on their own
    > until they become capable of dealing with the task at hand
    > under light supervision of someone conversant with that
    > same set of skills (which is usually me since it's a very
    > small team).
    >
    > 3. They're not capable of dealing with the task on their
    > own, but perfectly capable of doing it under mentoring of
    > someone conversant with the required skills (which is
    > usually me since it's a very small team).
    >
    > These three are the more or less workable options in my
    > experience.
    >
    > 4. They're not capable of dealing with the assigned task, so
    > instead of getting off their and working on it they ask
    > someone else to do it for them, please, pretty please?
    >
    > This one is not a workable option, on the other hand. In
    > fact, if one of my colleagues were doing that, I'd rip him
    > a new one, then recommend to either fire him or transfer to
    > a pure management position, where getting others to work
    > for free is a priceless skill.
    >[/ref]


    Hear! Hear!

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  19. #19

    Default OT WAS: Re: how many of a kind

    Joachim Durchholz <org> wrote in
    <f1v4l9$hs$de>: 
    >>
    >> Dear Sir,
    >>
    >> How kind it is of you to point out my awfully bad manners
    >> to me right after receiving some free advice[/ref]
    >
    > Communication in a newsgroup isn't broadband enough to
    > give you enough information to judge the other person, yet
    > you insist that you can judge him anyway.[/ref]

    Pardon me, 'judge him'? Can you please explain what you
    could possibly mean? My stating that his knowledge/skills
    in the field seem nonexistant? Could you perhaps point out
    to him that he shouldn't judge my manners on a basis of
    just one post? Could you perhaps point out the same thing
    to yourself?
     

    I don't believe I insisted on OP having to express his
    gratitude to me in my posts. In my opinion, the OP clearly
    demonstrated in his posts that he doesn't know a thing
    about MySQL. I gave him what I consider the best advice
    under the cirstances--to read an introductory
    book/tutorial on the matter. After that I remarked that his
    team leader might not like the fact that he's dabbling in
    something he's not qualified for; and that he doesn't seem
    to *try* to become qualified. In response, the OP said I
    should learn good manners. You seem to know what he meant
    by that, so could you please explain to me what kind of
    response he expected? Would that be a three-page "Joins
    101" lecture, despite the fact that such lectures can be
    easily found in precisely the introductory reading I
    recommended, and despite the fact that OP's understanding
    seems limited enough that perhaps it would be better for
    him to start with lectures on even more basic
    concepts,--and, mind you, such lectures can be easily found
    in precisely the introductory reading I recommended. I
    seriously don't get it. What did I do wrong to deserve
    a "thank you, you disgustingly rude person" response from
    the OP?
     

    My heart bleeds for good manners.

    --
    Pavel Lepin
    Pavel Guest

  20. #20

    Default Re: OT WAS: Re: how many of a kind

    On Thu, 10 May 2007 16:42:31 +0300, Pavel Lepin <com>
    wrote:
     

    SELECT *
    FROM worldwars
    WHERE wwID=3;
    subtenante Guest

Page 1 of 3 123 LastLast

Similar Threads

  1. what kind of .txt does CF generate?
    By AkronNick in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 8th, 04:11 AM
  2. because it is not the right kind of doent
    By Dawn Boyden in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 2
    Last Post: July 28th, 09:19 PM

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