Complex grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complex grouping

Author

Topic

faig
Starting Member

3 Posts

Posted - 2010-12-21 : 12:46:01

Hi everyone,

I have two dimensions.
PhoneType that may have one of 3 possible values (0, 1, 2).
CustomerType thet may have one of 2 possible values (1,2).

We assume that each customer may purchase up to 3 different phones. Below is a purchases table. I tried to randomize data

select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 1 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 2 as CustomerType, 1 as PhoneType1, 1 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 1 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 1 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 0 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 1 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 1 as PhoneType3

I need to get number of purchases per customer type and unique phone type regardless of the number of numbers and position in the same purchase. So for example the first records should be counted 1 for phonetype = 0 and one time for phonetype = 2 (even if customer bought 2 same type phones in the same order).

If I made my manual calculation correctly in the result I have to get below result

select 1 as CustomerType, 0 as PhoneType, 12 as Count
select 1 as CustomerType, 1 as PhoneType, 7 as Count Union ALL
select 1 as CustomerType, 2 as PhoneType, 10 as Count Union ALL
select 2 as CustomerType, 0 as PhoneType, 3 as Count Union ALL
select 2 as CustomerType, 1 as PhoneType, 5 as Count Union ALL
select 2 as CustomerType, 2 as PhoneType, 7 as Count Union ALL

Can anyone provide solution for this task?

Thans in advance

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 13:39:48

I hacked something out real quick. I think there are better ways, but off to a meeting:

WITH cte
AS
(
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 1 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 2 as CustomerType, 1 as PhoneType1, 1 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 1 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 1 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 0 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 1 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 1 as PhoneType3 
)

SELECT 
    CustomerType, 
    0 AS PhoneType, 
    SUM
    (
        CASE 
            WHEN 0 IN (PhoneType1, PhoneType2, PhoneType3)  THEN 1
            ELSE 0
        END 
    ) AS PhoneCount
FROM cte
GROUP BY CustomerType

UNION ALL

SELECT 
    CustomerType, 
    1 AS PhoneType, 
    SUM
    (
        CASE 
            WHEN 1 IN (PhoneType1, PhoneType2, PhoneType3)  THEN 1
            ELSE 0
        END 
    ) AS PhoneCount
FROM cte
GROUP BY CustomerType

UNION ALL

SELECT 
    CustomerType, 
    2 AS PhoneType, 
    SUM
    (
        CASE 
            WHEN 2 IN (PhoneType1, PhoneType2, PhoneType3)  THEN 1
            ELSE 0
        END 
    ) AS PhoneCount
FROM cte
GROUP BY CustomerType

faig
Starting Member

3 Posts

Posted - 2010-12-21 : 14:01:53

quote:

Originally posted by Lamprey

I hacked something out real quick. I think there are better ways, but off to a meeting:

WITH cte
AS
(
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 1 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 2 as CustomerType, 1 as PhoneType1, 1 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 1 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 1 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 0 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 1 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 1 as PhoneType3 
)

SELECT 
    CustomerType, 
    0 AS PhoneType, 
    SUM
    (
        CASE 
            WHEN 0 IN (PhoneType1, PhoneType2, PhoneType3)  THEN 1
            ELSE 0
        END 
    ) AS PhoneCount
FROM cte
GROUP BY CustomerType

UNION ALL

SELECT 
    CustomerType, 
    1 AS PhoneType, 
    SUM
    (
        CASE 
            WHEN 1 IN (PhoneType1, PhoneType2, PhoneType3)  THEN 1
            ELSE 0
        END 
    ) AS PhoneCount
FROM cte
GROUP BY CustomerType

UNION ALL

SELECT 
    CustomerType, 
    2 AS PhoneType, 
    SUM
    (
        CASE 
            WHEN 2 IN (PhoneType1, PhoneType2, PhoneType3)  THEN 1
            ELSE 0
        END 
    ) AS PhoneCount
FROM cte
GROUP BY CustomerType

This is proper solution which I had. I though about something more compact, becuase I have actually 13 different dimentions + 7 dimentions for Phonetypes. The query is going to be ugly :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 15:32:09

If you are not already locked in, can you change you schema to something better?

Here's another version that's alittle more compact:

WITH cte
AS
(
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 1 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 2 as CustomerType, 1 as PhoneType1, 1 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 1 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 1 as CustomerType, 0 as PhoneType1, 2 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 1 as PhoneType2, 2 as PhoneType3 Union ALL
select 1 as CustomerType, 1 as PhoneType1, 0 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 2 as PhoneType2, 1 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 1 as PhoneType2, 1 as PhoneType3 Union ALL
select 1 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 0 as PhoneType3 Union ALL
select 2 as CustomerType, 2 as PhoneType1, 0 as PhoneType2, 1 as PhoneType3 
)
,
cte2
AS 
(
    SELECT 
        CustomerType, 
        PhoneType1,
        PhoneType2,
        PhoneType3,
        ROW_NUMBER() OVER(ORDER BY CustomerType) AS RowNum
    FROM cte
)



SELECT CustomerType, PhoneType, COUNT(*)
FROM
(
    SELECT RowNum, CustomerType, PhoneType1 AS PhoneType
    FROM cte2
    UNION 
    SELECT RowNum, CustomerType, PhoneType2 AS PhoneType
    FROM cte2
    UNION 
    SELECT RowNum, CustomerType, PhoneType3 AS PhoneType
    FROM cte2
) AS T
GROUP BY CustomerType, PhoneType

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 15:48:45

One more reusing the cte2 from my previous post:

SELECT 
    CustomerType, 
    PhoneType, 
    COUNT(*)
FROM
(
    SELECT
        CustomerType, PhoneType
    FROM
        (SELECT RowNum, CustomerType, PhoneType1, PhoneType2, PhoneType3 FROM cte2) as p
    UNPIVOT
        (PhoneType FOR Foo IN (PhoneType1, PhoneType2, PhoneType3)) AS unpvt
    GROUP BY
        RowNum, CustomerType, PhoneType
) AS T
GROUP BY
    CustomerType, PhoneType

faig
Starting Member

3 Posts

Posted - 2010-12-21 : 16:13:46

quote:

Originally posted by Lamprey

One more reusing the cte2 from my previous post:

SELECT 
    CustomerType, 
    PhoneType, 
    COUNT(*)
FROM
(
    SELECT
        CustomerType, PhoneType
    FROM
        (SELECT RowNum, CustomerType, PhoneType1, PhoneType2, PhoneType3 FROM cte2) as p
    UNPIVOT
        (PhoneType FOR Foo IN (PhoneType1, PhoneType2, PhoneType3)) AS unpvt
    GROUP BY
        RowNum, CustomerType, PhoneType
) AS T
GROUP BY
    CustomerType, PhoneType

very nice solution sir. you have added PK into initial set

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources