I think what you are talking about could be called a 'crosstabulation' or
a crosstab.
Their are some tutorials about making cross-tabs using perl. I have used
them a lot, and they are really great.
I tend to stack up lots of IF statemens...
Table1
month person sex sales
1 a m 10
1 b f 20
1 c m 30
2 a m 40
2 b f 50
2 c m 60
select
month,
sum(if(sex='m',sales,0)) as male_sales,
sum(if(sex='f',sales,0)) as female_sales,
sum(sales) as total
from
Table1
group by
month;
This would give (I think)...
month male_sales female_sales total
1 40 20 60
2 100 50 150
You could easily add a...
count(distinct(if(sex='m',person,NULL))) as total_men,
count(distinct(if(sex='f',person,NULL))) as total_women,
to create average sales for men and women, or anything else you want.
Does that look right?
On Fri, 8 Apr 2005, Jacob, Raymond A Jr wrote:
>Question: I frequently would like to summarize the results of my query in
>heiarchical layout also
>known as a Pivot table.
>Here is an example of what I would like output. NULL will be printed as a
>space
>when output.
>
>sum of broken| source of | qty |reseller of |qty |customer with
>|qty by
> bolts |broken bolts|regional |broken bolts|reseller |broken bolts
> |customer
>-----------------------------------------------------------------------------
> 100 | NULL |NULL |NULL | NULL |NULL
> |NULL
>(100/NULL) | US | 75 |NULL | NULL |NULL
>|NULL
>(100/NULL) | (US/NULL) |(75/NULL) |ACME | 35 |NULL
>|NULL
>(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Barney Rubble |20
>(100/NULL) | US | 75 |NULL | NULL |NULL
>|NULL
>(100/NULL) | (US/NULL) |(75/NULL) |ACME | 35 |NULL
>|NULL
>(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Betty Rubble |5
>(100/NULL) | US | 75 |NULL | NULL |NULL
>|NULL
>(100/NULL) | (US/NULL) |(75/NULL) |ABLE | 25 |NULL
>|NULL
>(100/NULL) | (US/NULL) |(75/NULL) |(ABLE/NULL) |(25/NULL)|Sponge Bobble |20
>(100/NULL) | MEXICO | 15 |NULL | NULL |NULL
>|NULL
>(100/NULL) | (MEX/NULL) |(15/NULL) |TIPPY | 12 |NULL
>|NULL
>(100/NULL) | (MEX/NULL) |(15/NULL) |(ACME/NULL) |(12/NULL)|Juan Pepe |7
>
>
>100 * * * * * *
>* US 75 * * * *
>* * * ACME 35 * *
>* * * * * Barney Ruble 20
>
>Where * represents NULL or a Primary Key.
>
>How does one build a pivot table?
>from tables such as:
>
>factory_parts table
> ::{
>part no,
>plant,
>qty_manufactured
>plant name
>}
>reseller_parts table
> ::{
>part no
>plant
>qty received
>cost
>reseller name
>reseller id
>
>}
>customer_parts table
> ::{
>reseller id
>part no
>plant
>qty sold
>qty recvd
>customer id
>customer name
>}
>
>Ooops now the light bulb comes on
>I would do:
> select factory_parts.plant name,
> reseller_parts.reseller_name
> customer_parts.customer_name,
> customer_parts.qty_recvd
> from factory_parts,reseller_parts,customer_parts
> where customer_parts.part_no == 'broken_bolt' AND
> ( customer_parts.part_no == reseller_parts.part.no AND
> customer_parts.part_no == factory_parts.part.no )
>
>Now the question becomes how does one construct the aggregate columns
>representing the sum of bolts produced by the company,made at the plant,
>shipped to the reseller and sold to the customer,
>then join those aggregate columns? Any suggestions?
>
>Thank you,
>Raymond
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
