count()

Hello,

I have a table like tho following (I've simplified it):

ID WORD
__________
1 car
1 moto
1 wheel
2 house
2 apartment
.. ....


'ID' and 'word' are the "primary key".

Is it possible with one istruction to know how many WORDS there are for
each ID?
I'd want to obtain something like:

ID TOT_WORDS
______________
1 3
2 2
.. ...


--
rob4you

On 22 Feb, 15:29, rob4you <KIEDIM...@KIEDIMELO.IT> wrote:
> Hello,
>
> I have a table like tho following (I've simplified it):
>
> ID WORD
> __________
> 1 car
> 1 moto
> 1 wheel
> 2 house
> 2 apartment
> . ....
>
> 'ID' and 'word' are the "primary key".
>
> Is it possible with one istruction to know how many WORDS there are for
> each ID?
> I'd want to obtain something like:
>
> ID TOT_WORDS
> ______________
> 1 3
> 2 2
> . ...
>
> --
> rob4you

SELECT `id`, count(*) `tot_words`
FROM table
GROUP BY `id`

> SELECT `id`, count(*) `tot_words`
> FROM table
> GROUP BY `id`

Thank you very much!

I forgot the GROUP BY, so I had an error.

You're always precise in your post. :-)

--
rob4you

On 22 Feb, 16:45, rob4you <KIEDIM...@KIEDIMELO.IT> wrote:
> You're always precise in your post. :-)
I am, in what way? is that good?

Sorry, I've another doubt:

if the table is like that:

ID WORD TIMES
_________________
1 car 4
1 moto 5
1 wheel 2
2 house 3
2 apart 1
2 car 2
.. .... .

If I'd want to obtain something like that:

ID WORD TIMES TOTAL
_________________________
1 car 4 3
2 car 2 3
.. ... . .

That is:
- first field: ID
- second field: word (for example I can specify it with the WHERE
clause)
- third field: number of times the WORD appears in its own ID
- last field: number of TOTAL words which have the same ID

Is it possible?

--
rob4you

rob4you wrote:
> Hello,
>
> I have a table like tho following (I've simplified it):
>
> ID WORD
> __________
> 1 car
> 1 moto
> 1 wheel
> 2 house
> 2 apartment
> . ....
>
>
> 'ID' and 'word' are the "primary key".
>
> Is it possible with one istruction to know how many WORDS there are for
> each ID?
> I'd want to obtain something like:
>
> ID TOT_WORDS
> ______________
> 1 3
> 2 2
> . ...
>
>
> --
> rob4you
>
>

SELECT `ID`, COUNT(`WORD`) AS TOT_WORDS
GROUP BY ID
ORDER BY ID;

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

> SELECT `ID`, COUNT(`WORD`) AS TOT_WORDS
> GROUP BY ID
> ORDER BY ID;

Do you have an answer also to the other question I've posed?

--
rob4you

rob4you wrote:
> Sorry, I've another doubt:
>
> if the table is like that:
>
> ID WORD TIMES
> _________________
> 1 car 4
> 1 moto 5
> 1 wheel 2
> 2 house 3
> 2 apart 1
> 2 car 2
> . .... .
>
> If I'd want to obtain something like that:
>
> ID WORD TIMES TOTAL
> _________________________
> 1 car 4 3
> 2 car 2 3
> . ... . .
>
> That is:
> - first field: ID
> - second field: word (for example I can specify it with the WHERE clause)
> - third field: number of times the WORD appears in its own ID
> - last field: number of TOTAL words which have the same ID
>
> Is it possible?
>
> --
> rob4you
>
>

I don't think it would be possible as you have it laid out. The problem
is you want a count for one id/word combination, but in the same result
you want the count for all the words in the id.

If you put your word in the WHERE clause, SQL will filter out all the
other words in the id. I'd suggest two SQL statements.

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

rob4you wrote:
> Sorry, I've another doubt:
>
> if the table is like that:
>
> ID WORD TIMES
> _________________
> 1 car 4
> 1 moto 5
> 1 wheel 2
> 2 house 3
> 2 apart 1
> 2 car 2
> . .... .
>
> If I'd want to obtain something like that:
>
> ID WORD TIMES TOTAL
> _________________________
> 1 car 4 3
> 2 car 2 3
> . ... . .
>
> That is:
> - first field: ID
> - second field: word (for example I can specify it with the WHERE
> clause)
> - third field: number of times the WORD appears in its own ID
> - last field: number of TOTAL words which have the same ID
>
> Is it possible?

I don't understand what "number of TOTAL words which have the same ID"
It makes no sense.

Please offer sample data complete with sample results so that I can see what
you mean.

> Please offer sample data complete with sample results so that I can see what
> you mean.

Ok, i'll try. :-)

Tabele 'words' contains these data:

catID word occurrencies
_________________________________
1 audi 154
1 alfa 95
1 bmw 132
2 wheels 56
2 audi 80
3 ferrari 15

Knowing just the word I want to search (e.g. 'audi'), I want to obtain,
if possible with a single query (or with some sub-queries), the
following results:


catID word occurr word_tot tot_in_catID
__________________________________________________ ______
1 audi 154 (154+80) 3
2 audi 80 (154+80) 2


That is, I want to get:

- the categories (catID) where the word 'audi' appears
- the word 'audi' (of course 'audi' is the word I already know)
- the number of occurencies of the word 'audi' according to each
category (this is exactly the 'occurrencies' field in the main table)
- the sum of occurrencies fields where there is the word 'audi' in the
various categories (that is 154+80). Of course this value is the same
for each row of the results.
- the total number of words in each category (as you can see in
category 1 the words are '3', in category 2 they are '2').

Can you help me?

--
rob4you