SQL – Count combinations of values in several columns

You have a large table (in my case it has 8,697,647 records) with an Asset Number and a Business Unit, and you would like to know if any asset has record with multiple Business Units.

You can count records of unique Asset/BU combinations, but this is not what you want:

SELECT FLNUMB, FLMCU, COUNT(FLAID) AS COUNT
FROM JDE_CRP.CRPDTA.F1202 
GROUP BY FLNUMB, FLMCU
ORDER BY FLNUMB, FLMCU

This query will return results similar to these:

FLNUMB	FLMCU	COUNT
84865	12000	66
84866	12000	66
84867	12000	75
84867	12704	18
84867	12705	28
84868	12000	75
84868	12715	22
84868	12717	32
84869	12000	66
84870	12000	75
84870	12535	40
84871	12000	66
84872	12000	66

 

To get to a desired output, you need to wrap the results of query above and count if any Asset belongs to more than one BU:

SELECT A.FLNUMB, MIN(A.FLMCU) AS [Min BU], 
  MAX(A.FLMCU) AS [Max BU], COUNT(A.FLNUMB) AS COUNT 
FROM ( SELECT FLNUMB, FLMCU 
    FROM JDE_CRP.CRPDTA.F1202 GROUP BY FLNUMB, FLMCU ) A
GROUP BY A.FLNUMB
HAVING COUNT(*) > 1
ORDER BY A.FLNUMB

This query will return results similar to these:

FLNUMB	Min BU	Max BU	COUNT
84853	14046	14054	2
84857	14023	14046	2
84867	12000	12705	3
84868	12000	12717	3
84870	12000	12535	2
85697	12000	12750	4
85711	14046	14054	2

 

Special feature of this output is that we not only listing Assets that have depreciation in multiple Business Units, but we even provide MIN and MAX Business Units as a point of reference.

To make our task even more interesting, let’s count number of combinations of three fields:

– Asses Number,
– Business Unit and
– Fiscal Year.

As in previous case, we start with sub-query:

SELECT FLNUMB, FLMCU, FLFY, COUNT(*)
FROM F1202  
GROUP BY FLNUMB, FLMCU, FLFY
ORDER BY FLNUMB, FLMCU, FLFY

 

This query will return results similar to these:

FLNUMB	FLMCU	FLFY	COUNT
25747	10654	13	9
74774	10691	12	9
40551	10501	9	9
72253	10575	8	9
27055	10691	6	9
36794	10760	14	6
52905	10691	12	6
112074	12995	17	20
37149	10707	6	6
39456	10691	15	6

 

Now. Let’s wrap the sub-query onto another SELECT statement:

SELECT A.FLNUMB, A.FLMCU, COUNT(A.FLNUMB) AS COUNT FROM
( SELECT FLNUMB, FLMCU, FLFY
FROM F1202 GROUP BY FLNUMB, FLMCU, FLFY ) A
GROUP BY A.FLNUMB, A.FLMCU
HAVING COUNT(*) > 1

 

Statement above will return results similar to these:

FLNUMB	FLMCU	COUNT
85311	10672	10
18801	14202	13
80122	10566	11
40774	10754	5
74281	10792	12
49796	10633	13
69883	10692	13

 

You can even bring in MIN and MAX fiscal years for each unique Asset/BU combination:

SELECT A.FLNUMB, A.FLMCU, MIN(FLFY) AS [Min FY], 
  MAX(FLFY) AS [Max FY], COUNT(A.FLNUMB) AS COUNT 
FROM ( SELECT FLNUMB, FLMCU, FLFY, COUNT(FLAID) AS COUNT
    FROM F1202 GROUP BY FLNUMB, FLMCU, FLFY ) A
GROUP BY A.FLNUMB, A.FLMCU
HAVING COUNT(*) > 1
ORDER BY A.FLNUMB, A.FLMCU

 

Statement above will return results similar to these:

FLNUMB	FLMCU	Min FY	Max FY	COUNT
72196	10000	6	17	12
75179	10025	7	17	11
68802	10040	5	6	2
68814	10040	5	6	2
68825	10040	5	6	2
68836	10040	5	6	2
35112	10042	5	8	4

 

It would be interesting to hear, if counting unique combinations of two or more fields in a table is possible without using a subquery.