From: Joerg Bruehe (joergmysql.com)
Date: Fri Jul 18 2008 - 03:46:33 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi Ron !

Ron wrote:
> -----------------------------------------------------------------------------------------------------------------------------------------
>
> | customercode | customer name | customerphonenumber | callednumber |
> calldate | callstart | callend | callduration | callcost |
> -----------------------------------------------------------------------------------------------------------------------------------------
>
> | 10000116 | Company Name | Customer NUmber | 0061XXXXXXXXX |
> 2008-07-16 | 15:25:19 | 15:26:00 | 1 | 0.229375 |
> | 10000116 | Company Name | Customer NUmber | 0063XXXXXXXXX |
> 2008-07-16 | 15:25:21 | 15:26:44 | 2 | 0.07759 |
> | 10000116 | Company Name | Customer NUmber | 001XXXXXXXXXX |
> 2008-07-16 | 15:25:24 | 15:25:34 | 1 | 0.229375 |
> | 10000116 | Company Name | Customer NUmber | 0060XXXXXXXXX |
> 2008-07-16 | 15:25:25 | 15:29:33 | 5 | 1.14688 |
> [[...]]
> ------------------------------------------------------------------------------------------------------------------------------------------
>
>
> Hi,
>
> How can i count by countrycode with that table? I kind of need a summary
> to count how many calls went to certain country. Thank You. I hope my
> question is clear.

The question is two-fold:

1) Use "SELECT COUNT(*) FROM tab GROUP BY countrycode".

2) You don't have countrycode as a column, so
    a) either you add it, filled by a trigger,
    b) or you use an expression to calculate the "countrycode" value from
       "callednumber" and use that for GROUP BY, see
       http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

I fear both approaches won't bee too easy, because the length of the
countrycode isn't constant.

I *assume* you need a list of the countrycode values (separate table)
and use that for the transformation, but I haven't thought about that
sufficiently to make a proposal.

Jörg

--
Joerg Bruehe, MySQL Build Team, joergmysql.com
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]