List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 4 1999 7:22pm
Subject:Re: Combine then group
View as plain text  
Efi Gabriel wrote:
> 
> Hello there,
> 
> I have this table.
> 
> +--------------+--------------+-----------+
> | helper1_name | helper2_name | logp_d_no |
> +--------------+--------------+-----------+
> | Tony         | Robert       |    643410 |
> | Tony         | Robert       |    643428 |
> | Tony         | Manny        |    644665 |
> | Tony         | Jani         |    816663 |
> | Manny        | Jani2        |    644628 |
> | Tony         | Robert       |    643516 |
> | Jani2        | Robert       |    644725 |
> | Tony         | Robert       |    644750 |
> | Jani         | Robert       |    644791 |
> | Tony         | Robert       |    644811 |
> | Tony         | Robert       |    644935 |
> | Jani         | Jani2        |    643599 |
> | Jani         | Jani2        |    459466 |
> | Tony         | Robert       |    677788 |
> | Jani         | Jani2        |    677571 |
> | Jani         | Jani2        |    677914 |
> | Jani         | Jani2        |    677845 |
> | Jani         | Jani2        |    677988 |
> | Jani         | Jani2        |    679558 |
> | Jani2        | Manny        |    678190 |
> +--------------+--------------+-----------+
> 
> mysql> select helper1_name,count(logp_d_no) from driver_salary group by
> helper1_name;
> +--------------+------------------+
> | helper1_name | count(logp_d_no) |
> +--------------+------------------+
> | Jani         |                8 |
> | Jani2        |                2 |
> | Manny        |                1 |
> | Tony         |                9 |
> +--------------+------------------+
> 4 rows in set (0.01 sec)
> 
> mysql> select helper2_name,count(logp_d_no) from driver_salary group by
> helper2_name;
> +--------------+------------------+
> | helper2_name | count(logp_d_no) |
> +--------------+------------------+
> | Jani         |                1 |
> | Jani2        |                8 |
> | Manny        |                2 |
> | Robert       |                9 |
> +--------------+------------------+
> 4 rows in set (0.00 sec)
> 
> Is it possible to combine helper1_name and helper2_name as helper and then
> group it.
> In other words I want the output like this. Also adding the count(logp_d_no)
> together.
> 
> +--------------+------------------+
> | helper       | count(logp_d_no) |
> +--------------+------------------+
> | Jani         |                9 |
> | Jani2        |               10 |
> | Manny        |                3 |
> | Robert       |                9 |
> | Tony         |                9 |
> +--------------+------------------+
> 
> Thanks for your patience, (In case)
> 
> Efi

Hi Efi

This is only possible by creating a temporary table, because mysql doesnt support UNION's.
1) CREATE TABLE tmp (helper char(10))
2) INSERT INTO tmp SELECT helper1_name FROM driver_salary
3) INSERT INTO tmp SELECT helper2_name FROM driver_salary
4) SELECT helper, count(*) FROM tmp GROUP BY helper

Tschau
Christian

Thread
Combine then groupEfi Gabriel4 Jun
  • Re: Combine then groupChristian Mack4 Jun