Help on query report...

dear all,

i have this table

id | contract No | site id | handover date |status| finishing date |
contract value|actual value
1 | T0001 | LOS01 | 12-Apr-06 | Done | 11-Apr-06 |
12,000 | 12,000
2 | T0002 | LOS02 | 12-Apr-06 | Done | 11 Apr-06 |
11,000 | 11,000
3 | T0001 | LOS02 | | Done | 13-Apr-06
| 13,000 | 13,000
4 | T0003 | LOS01 | | Cancel |
| 11,000 |

now my manager asked me to produce summary report like :

Contract No | TOTAL site | Total Cancelled | Total Active | Handovered
| Contract Value |
T0001 | 2 | 0 | 2
| 1 | 25,000 |
T0002 | 1 | 0 | 1
| 1 | 11,000 |
T0003 | 1 | 1 | 0
| 0 | 11,000 |


is there some way to do it in mySQL? i'm using php 5 and mysql 5.1
right now i'm doing it manually, but i thinks it was really
complex...Could somebody help me with the query ?
many thanks,

br


--
Jangan tanyakan apa yang Indonesia telah berikan pada mu
tapi bertanyalah apa yang telah engkau berikan kepada Indonesia

-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GU/IT d- s: a-- C++ UL P L++ E W++ N* o-- K-
w PS Y-- PGP- t++ 5 X R++ tv b+ DI D+ G e+ h* r- z?
------END GEEK CODE BLOCK------

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Help on query report...

Suprie wrote:
> dear all,
>
> i have this table
>
> id | contract No | site id | handover date |status| finishing date |
> contract value|actual value
> 1 | T0001 | LOS01 | 12-Apr-06 | Done | 11-Apr-06 |
> 12,000 | 12,000
> 2 | T0002 | LOS02 | 12-Apr-06 | Done | 11 Apr-06 |
> 11,000 | 11,000
> 3 | T0001 | LOS02 | | Done | 13-Apr-06
> | 13,000 | 13,000
> 4 | T0003 | LOS01 | | Cancel |
> | 11,000 |
>
> now my manager asked me to produce summary report like :
>
> Contract No | TOTAL site | Total Cancelled | Total Active | Handovered
> | Contract Value |
> T0001 | 2 | 0 | 2
> | 1 | 25,000 |
> T0002 | 1 | 0 | 1
> | 1 | 11,000 |
> T0003 | 1 | 1 | 0
> | 0 | 11,000 |
>
>
> is there some way to do it in mySQL? i'm using php 5 and mysql 5.1
> right now i'm doing it manually, but i thinks it was really
> complex...Could somebody help me with the query ?
> many thanks,

Show us the query you already have rather than us just guessing the
tables etc.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Help on query report...

goolge cross tab query ... that is what you are looking for

bastien


>From: Suprie
>To: php-db@lists.php.net
>Subject: [PHP-DB] Help on query report...
>Date: Wed, 15 Nov 2006 13:28:15 +0700
>
>dear all,
>
>i have this table
>
>id | contract No | site id | handover date |status| finishing date |
>contract value|actual value
>1 | T0001 | LOS01 | 12-Apr-06 | Done | 11-Apr-06 |
>12,000 | 12,000
>2 | T0002 | LOS02 | 12-Apr-06 | Done | 11 Apr-06 |
>11,000 | 11,000
>3 | T0001 | LOS02 | | Done | 13-Apr-06
> | 13,000 | 13,000
>4 | T0003 | LOS01 | | Cancel |
> | 11,000 |
>
>now my manager asked me to produce summary report like :
>
>Contract No | TOTAL site | Total Cancelled | Total Active | Handovered
>| Contract Value |
>T0001 | 2 | 0 | 2
> | 1 | 25,000 |
>T0002 | 1 | 0 | 1
> | 1 | 11,000 |
>T0003 | 1 | 1 | 0
> | 0 | 11,000 |
>
>
>is there some way to do it in mySQL? i'm using php 5 and mysql 5.1
>right now i'm doing it manually, but i thinks it was really
>complex...Could somebody help me with the query ?
>many thanks,
>
>br
>
>
>--
>Jangan tanyakan apa yang Indonesia telah berikan pada mu
>tapi bertanyalah apa yang telah engkau berikan kepada Indonesia
>
>-----BEGIN GEEK CODE BLOCK-----
>Version: 3.1
>GU/IT d- s: a-- C++ UL P L++ E W++ N* o-- K-
>w PS Y-- PGP- t++ 5 X R++ tv b+ DI D+ G e+ h* r- z?
>------END GEEK CODE BLOCK------
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

____________________________________________________________ _____
Ready for the world's first international mobile film festival celebrating
the creative potential of today's youth? Check out Mobile Jam Fest for your
a chance to WIN $10,000! www.mobilejamfest.com

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php