Count by using combinations - 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

Count by using combinations

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2012-10-24 : 11:25:26

Hi,

I have the following:

Customer_ID Toll_Count Toll_Date
12345 1 Jan 2011
12345 2 Feb 2011
12345 0 March 2011
12345 1 April 2011
43567 1 Jan 2011
43567 2 Feb 2011
43567 0 March 2011
43567 1 April 2011

I want to show in my final output - count the customers who have the same combination of toll_count for Jan, Feb, March and April.
So in the above it will be the following. There could be various combinationsand my toll_date runs from Jan 2011 to Dec 2011.

Counts
2

Below is my query

select o.customer_id, a.customer_toll_id, a.toll_date
into #Toll
from orders o with(nolock), toll_report a with(nolock)
where o.order_id = a.order_id and
a.toll_type_id in ('g','r') and
a.toll_date >= '01/01/2011' and
a.toll_date < '01/01/2012'

select customer_id,
COUNT(distinct customer_toll_id) as 'count',
CONVERT(varchar(6),toll_date,112) as 'toll_date'
into #Toll_count
from #Toll
group by customer_id, CONVERT(varchar(6),toll_date,112)

select * from #Toll_count

Thanks in advance,
Petronas

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-24 : 12:41:11

There is probalby a better way to solve this, but maybe this will help:

DECLARE @Toll TABLE(Customer_ID INT, Toll_Count INT, Toll_Date VARCHAR(20))
INSERT @Toll Values
(12345, 1, 'Jan 2011'),
(12345, 2, 'Feb 2011'),
(12345, 0, 'March 2011'),
(12345, 1, 'April 2011'),
(43567, 1, 'Jan 2011'),
(43567, 2, 'Feb 2011'),
(43567, 0, 'March 2011'),
(43567, 1, 'April 2011')

SELECT  
    COUNT(DISTINCT A.Customer_ID)
FROM
    (
        SELECT A1.* FROM
        (SELECT * FROM @Toll WHERE Toll_Date = 'Jan 2011') AS A1
        INNER JOIN
        (SELECT * FROM @Toll WHERE Toll_Date = 'Jan 2011') AS A2
        ON A1.Customer_ID <> A2.Customer_ID
        AND A1.Toll_Count = A2.Toll_Count
    ) AS A
INNER JOIN
    (
        SELECT A1.* FROM
        (SELECT * FROM @Toll WHERE Toll_Date = 'Feb 2011') AS A1
        INNER JOIN
        (SELECT * FROM @Toll WHERE Toll_Date = 'Feb 2011') AS A2
        ON A1.Customer_ID <> A2.Customer_ID
        AND A1.Toll_Count = A2.Toll_Count
    ) AS B
    ON A.Customer_ID = B.Customer_ID
INNER JOIN
    (
        SELECT A1.* FROM
        (SELECT * FROM @Toll WHERE Toll_Date = 'March 2011') AS A1
        INNER JOIN
        (SELECT * FROM @Toll WHERE Toll_Date = 'March 2011') AS A2
        ON A1.Customer_ID <> A2.Customer_ID
        AND A1.Toll_Count = A2.Toll_Count
    ) AS C
    ON B.Customer_ID = C.Customer_ID
INNER JOIN
    (
        SELECT A1.* FROM
        (SELECT * FROM @Toll WHERE Toll_Date = 'April 2011') AS A1
        INNER JOIN
        (SELECT * FROM @Toll WHERE Toll_Date = 'April 2011') AS A2
        ON A1.Customer_ID <> A2.Customer_ID
        AND A1.Toll_Count = A2.Toll_Count
    ) AS D
    ON C.Customer_ID = D.Customer_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 12:55:19

this is another way


;With Temp
AS
(
SELECT Customer_ID,
STUFF((SELECT ',' + CAST(Toll_Count AS varchar(10)) + '-' + Toll_Date
FROM Table WHERE Customer_ID = t.Customer_ID ORDER BY Toll_Count,Toll_Date FOR XML PATH('')),1,1,'') AS CountList
FROM (SELECT DISTINCT Customer_ID FROM Table) t
)

SELECT COUNT(Customer_ID)
FROM Temp
GROUP BY CountList

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources