COUNT(*) OVER (PARTITION BY... HOW TO

COUNT() OVER() for a partition by concatenation is the same as doing it just by the 2 columns:

select * from mytable;
+------+-----+
| ITEM | REV |
+------+-----+
| a    | Q   |
| b    | E   |
| c    | D   |
| d    | Q   |
| e    | E   |
| f    | D   |
| a    | Q   |
| b    | E   |
| c    | D   |
| d    | Q   |
| e    | E   |
| f    | D   |
+------+-----+

Select allOpen in new window

SELECT
  ITEM
, REV
, COUNT(*) OVER(PARTITION BY ITEM||REV) CONCAT_COUNT
, COUNT(*) OVER(PARTITION BY ITEM, REV) COLUMN_COUNT
FROM MYTABLE;

+------+-----+--------------+--------------+
| ITEM | REV | CONCAT_COUNT | COLUMN_COUNT |
+------+-----+--------------+--------------+
| a    | Q   |            2 |            2 |
| a    | Q   |            2 |            2 |
| b    | E   |            2 |            2 |
| b    | E   |            2 |            2 |
| c    | D   |            2 |            2 |
| c    | D   |            2 |            2 |
| d    | Q   |            2 |            2 |
| d    | Q   |            2 |            2 |
| e    | E   |            2 |            2 |
| e    | E   |            2 |            2 |
| f    | D   |            2 |            2 |
| f    | D   |            2 |            2 |
+------+-----+--------------+--------------+

Select allOpen in new window

online demo

Maybe you are after something like "concat_all_count" below, note that the "concat_count" would always be 1 so it may be ignored, just there to highlight what it does:

SELECT
  ITEM
, REV
, COUNT(distinct ITEM||REV) OVER(PARTITION BY ITEM||REV) CONCAT_COUNT
, COUNT(distinct ITEM||REV) OVER()                       CONCAT_all_COUNT
, COUNT(*) OVER(PARTITION BY ITEM, REV) COLUMN_COUNT
FROM MYTABLE;


+------+-----+--------------+------------------+--------------+
| ITEM | REV | CONCAT_COUNT | CONCAT_ALL_COUNT | COLUMN_COUNT |
+------+-----+--------------+------------------+--------------+
| a    | Q   |            1 |                6 |            2 |
| a    | Q   |            1 |                6 |            2 |
| b    | E   |            1 |                6 |            2 |
| b    | E   |            1 |                6 |            2 |
| c    | D   |            1 |                6 |            2 |
| c    | D   |            1 |                6 |            2 |
| d    | Q   |            1 |                6 |            2 |
| d    | Q   |            1 |                6 |            2 |
| e    | E   |            1 |                6 |            2 |
| e    | E   |            1 |                6 |            2 |
| f    | D   |            1 |                6 |            2 |
| f    | D   |            1 |                6 |            2 |
+------+-----+--------------+------------------+--------------+

Select allOpen in new window

online demo

None of the options above work on my two columns. Only when I run the function after concatenating does it work. My table has many other columns so possibly that is why?

You don't really provide sufficient facts for a full answer, these 3 things assist:

a. sample data
b. expected result
c. existing code

nb. All we can see about your issue is what is available on this page.

This is self-explanatory. Multiple columns in a table, two columns need to be concatenated and counted. If I concatenate within a subquery and use the count by partition it works, but does not work directly on concatenated columns and does not work partitioning the columns separately. I cannot supply a sample but this is self-explanatory.

Could be your sample has all counts of 2 and no single values which is why it works.

Oh well. I'm just not able to understand your expected result. Of course I can access tables with multiple columns, but only you have the specific data, and the specific result that you want from that data. Frequently this will seem self evident to the asker... but that's because you are familiar with the data and what you want from it.

I also don't follow why I should create sample data, you already have that. It would help me/us to answer your problem if you provided some.

Hope you get a solution.

I don't know what to tell you, it is just two columns concatenated, does not matter the value of the data, character data. Your little sample is probably too simplified because you don't have other columns and all values match. My data results is coming from joined tables and is combined with other joined tables which is probably causing the problem. Apparently the data has to be rendered.  

I'm going to leave this open another day to see if anyone else knows why this would ever happen. In meantime, I am going to concatenate in subquery and query the count in main query. That does work.

Right, I understand. The issue is with nulls from the joins.