Re: Pivot / Crosstab With Count Unique data.
Date: Wed, 16 Jul 2008 23:40:26 -0700 (PDT)
Message-ID: <6db9d039-09d5-4dc6-bbd1-d2f6a0c625df_at_z72g2000hsb.googlegroups.com>
On Jul 17, 1:55 pm, Lemune <alfredosilito..._at_gmail.com> wrote:
> Hi,
>
> I'm trying to create pivot, my data are area, sub area, member code.
> On Pivot/Crosstab data area i want to calculate how many member that
> access in sub area, and how many member that access in area, where as
> on the raw data it self my member has many record on each area and sub
> area. My be this will make an sample
>
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member2
> * AreaA SubAreaA Member3
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member2
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member3
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member2
> * AreaA SubAreaA Member3
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member1
> * AreaA SubAreaA Member2
> * AreaA SubAreaA Member1
> * AreaA SubAreaB Member3
> * AreaA SubAreaB Member1
> * AreaA SubAreaB Member1
> * AreaA SubAreaB Member2
> * AreaA SubAreaB Member3
> * AreaA SubAreaB Member1
> * AreaB SubAreaA Member1
> * AreaB SubAreaB Member2
> * AreaB SubAreaA Member1
> * AreaB SubAreaB Member3
> * AreaB SubAreaA Member1
>
> The result of my pivot is That I want:
>
> Sub Area
>
> Area SubAreaA SubAreaB Total
>
> AreaA 3
> 3 3
>
> AreaB 1
> 2 3
>
> Total 3
> 2 3
>
> Could we create this kind of pivot?
>
> If it could be done, how we do it?
>
> Thanks in advanced
I think 4 different select queries are needed
(2x2 submatrix in top left)
SELECT area, subarea, COUNT(DISTINCT member) FROM mytable
(1x2 submatrix in bottom left)
SELECT subarea, COUNT(DISTINCT member) FROM mytable
(2x1 submatrix in top right)
SELECT area, COUNT(DISTINCT member) FROM mytable
(1x1 submatrix in bottom right)
SELECT COUNT(DISTINCT member) FROM mytable
Received on Thu Jul 17 2008 - 08:40:26 CEST