List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 4 2005 7:47pm
Subject:Re: Query Question
View as plain text  
Roy Harrell <1600uVision@stripped> wrote on 10/04/2005 
03:15:33 PM:

> Suppose I have a simple table as follows:
> 
> PartName   Tolerance   Cycles
> A      1      10
> A      2      11
> A      3      13
> A      4      15
> A      5      18
> B      1      12
> B      2      14
> B      3      16
> B      4      16
> B      5      17
> C      1      6
> C      2      7 
> C      3      7
> C      4      8
> C      5      10
> 
> 
> How do I set up a query whose output would 
> look like this:
> 
> Tolerance   PartA   PartB   PartC
> 1      10   12   6
> 2      11   14   7
> 3      13   16   7
> 4      15   16   8
> 5      18   17   10
> 
> 
> Thanks,
> 
> Roy Harrell
> Adaptive Equipment
> 352.372.7821
> 
What you are doing is called a Pivot Table or a Crosstab Query. In order 
to do one with MySQL, you need to decide what kind of information you want 
in your "middle cells". That's because you need to pick an aggregate 
function (sum, min, max, avg, etc.) to wrap around your middle cell data. 
I will break it down by min cycles, max cycles, and avg cycles for each of 
the parts A, B, and C.


SELECT 
        Tolerance
        , MIN(if(PartName='A', cycles, NULL)) as A_min
        , MAX(if(PartName='A', cycles, NULL)) as A_max
        , AVG(if(PartName='A', cycles, NULL)) as A_avg
        , MIN(if(PartName='B', cycles, NULL)) as B_min
        , MAX(if(PartName='B', cycles, NULL)) as B_max
        , AVG(if(PartName='B', cycles, NULL)) as B_avg
        , MIN(if(PartName='C', cycles, NULL)) as C_min
        , MAX(if(PartName='C', cycles, NULL)) as C_max
        , AVG(if(PartName='C', cycles, NULL)) as C_avg
FROM simpletable
GROUP BY Tolerance;

There is no "automatic" solution for this with MySQL. You have to know 
your what your analysis columns will be and how to identify them before 
you can write this query. Nothing says you can't use code to write the 
query (notice the repetitive pattern?) but there is no native function to 
do that for you.

Notes: When computing MIN(), MAX(), or AVG(), all NULL values should be 
ignored. That's what the IF() clause is for, to pick out just the values 
you want to analyze.

Does that help?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine





Thread
Query QuestionRoy Harrell4 Oct
  • Re: Query QuestionDavid Griffiths4 Oct
  • Re: Query QuestionSGreen4 Oct
  • Re: Query QuestionPeter Brawley5 Oct
RE: Query QuestionJacek Becla4 Oct
  • RE: Query QuestionSGreen4 Oct
RE: Query QuestionJacek Becla4 Oct
  • RE: Query QuestionRoy Harrell4 Oct