SEO

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!

On May 9, 5:29 pm, Stefano Perna <ajajaja...@tin.it> wrote:
> 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!

select count(product_name) from table group by cat_id

strawberry wrote:
>
> select count(product_name) from table group by cat_id
>



very good, thanks!

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

On May 9, 6:42 pm, Stefano Perna <ajajaja...@tin.it> wrote:
> strawberry wrote:
>
> > select count(product_name) from table group by cat_id
>
> very good, thanks!
>
> Is the result always ordered? That is, do I receive data as they were
> ordered by cat_id?

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 ha scritto:

>
> 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.
>



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 Perna wrote:
> strawberry ha scritto:
>
>>
>> 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.
>>
>
>
>
> 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!!
>
>
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 -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".

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
>



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

Thanks for pointing that out!

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!

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

>
>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!

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 ha scritto:

>
> 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



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