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:

Life is a Passage

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.

(Visited 31 times, 1 visits today)

Be the first to comment

Your question, correction or clarification Ваш вопрос, поправка или уточнение

Editor on duty will review your comment. Can't wait? Discuss your topic right now in forums, if you register and login.     Required fields are marked with * Ваше послание пойдет на просмотр в редакцию. Не можете ждать? Обсудите ваш вопрос прямо сейчас в форуме Русский Круг , ecли вы зарегистрируетесь и войдёте под своим именем.     Поля, обязательные для заполнения, помечены *
* *