# 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.

(Visited 1,586 times, 2 visits today)