Counting records when using group by

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Counting records when using group by

Ian Blackford
Hi,

I currently have a list like this:

646726-ianblackford
647626-ianblackford
123456-somebody
123456-somebody
123456-somebody
987654-newperson

This is the contents of a field inside the records tags, but I really  
want to end up with this:

Name                        Occurrence
646726-ianblackford      2
123456-somebody         3
987654-newperson        1

I have looked at the mysql COUNT feature combined with GROUP BY, but  
I don't know how to apply it to get the above list. Anybody got a  
neat way of doing the above?



Best Regards

ICB

---------------

. Ian Blackford

. Tel:       01952 503459
. Mobile:  07970 250168
. web:      http://www.designconscious.com/
. Skype:   designconscious.com


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Counting records when using group by

Göran Törnquist
Something like this should solve your puzzle.

SELECT somefield, COUNT(somefield) FROM sometable GROUP BY somefield;

Hope it helps.

/Göran

>Hi,
>
>I currently have a list like this:
>
>646726-ianblackford
>647626-ianblackford
>123456-somebody
>123456-somebody
>123456-somebody
>987654-newperson
>
>This is the contents of a field inside the
>records tags, but I really want to end up with
>this:
>
>Name                        Occurrence
>646726-ianblackford      2
>123456-somebody         3
>987654-newperson        1
>
>I have looked at the mysql COUNT feature
>combined with GROUP BY, but I don't know how to
>apply it to get the above list. Anybody got a
>neat way of doing the above?
>
>
>
>Best Regards
>
>ICB
>
>---------------
>
>. Ian Blackford
>
>. Tel:       01952 503459
>. Mobile:  07970 250168
>. web:      http://www.designconscious.com/
>. Skype:   designconscious.com
>
>
>--
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:
>http://www.listsearch.com/lassotalk.lasso?manage


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Counting records when using group by

Peter (rcmf)
In reply to this post by Ian Blackford
Ian,

Try this:

SELECT name, COUNT(*)
FROM table
GROUP BY name

Peter A
Web Developer
RCMF


"If you try the best you can
  The best you can is good enough"
--Radiohead


On May 31, 2005, at 2:05 PM, Ian Blackford wrote:

> Hi,
>
> I currently have a list like this:
>
> 646726-ianblackford
> 647626-ianblackford
> 123456-somebody
> 123456-somebody
> 123456-somebody
> 987654-newperson
>
> This is the contents of a field inside the records tags, but I really
> want to end up with this:
>
> Name                        Occurrence
> 646726-ianblackford      2
> 123456-somebody         3
> 987654-newperson        1
>
> I have looked at the mysql COUNT feature combined with GROUP BY, but I
> don't know how to apply it to get the above list. Anybody got a neat
> way of doing the above?
>
>
>
> Best Regards
>
> ICB
>
> ---------------
>
> . Ian Blackford
>
> . Tel:       01952 503459
> . Mobile:  07970 250168
> . web:      http://www.designconscious.com/
> . Skype:   designconscious.com
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Counting records when using group by

Ian Blackford
In reply to this post by Ian Blackford

On May 31, 2005, at 20:13, Göran Törnquist wrote:

> SELECT somefield, COUNT(somefield) FROM sometable GROUP BY somefield;
>

Thanks for that, but how do I display the COUNT value ?

ie:

   NAME                       OCCURRENCE
[Records]
   [Field:'somefield']     [Field:  ???????]
[/Records]

Best Regards

ICB

---------------

. Ian Blackford

. Tel:       01952 503459
. Mobile:  07970 250168
. web:      http://www.designconscious.com/
. Skype:   designconscious.com


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Counting records when using group by

Jim Van Heule
In reply to this post by Ian Blackford
> Thanks for that, but how do I display the COUNT value ?

Basically, I name it in the query.


SELECT name, COUNT(*) AS happy
FROM table
GROUP BY name

Field:'happy';


Jim Van Heule
[hidden email]


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Counting records when using group by

Peter (rcmf)
In reply to this post by Ian Blackford
Ian

Do this:

SELECT name, COUNT(*) as nameCount
FROM table
GROUP BY name

records;
        field: 'name'; '\t';
        field: 'nameCount';  '<br>';
/records;

Peter A
Web Developer
RCMF


"If you try the best you can
  The best you can is good enough"
--Radiohead


On May 31, 2005, at 2:19 PM, Ian Blackford wrote:

>
> On May 31, 2005, at 20:13, Göran Törnquist wrote:
>
>> SELECT somefield, COUNT(somefield) FROM sometable GROUP BY somefield;
>>
>
> Thanks for that, but how do I display the COUNT value ?
>
> ie:
>
>   NAME                       OCCURRENCE
> [Records]
>   [Field:'somefield']     [Field:  ???????]
> [/Records]
>
> Best Regards
>
> ICB
>
> ---------------
>
> . Ian Blackford
>
> . Tel:       01952 503459
> . Mobile:  07970 250168
> . web:      http://www.designconscious.com/
> . Skype:   designconscious.com
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage