I have one table where each row has three columns. The first two columns are a prefix and a value. The third column is what I'm trying to get a distinct count for columns one/two.

Basically I'm trying to get to this.

I've tried a lot of different versions but I'm basically noodling around this.

select prefix||value as Account, count(distinct thirdcolumn) as Totals from Transactions

CodePudding user response：

It sounds like you want

SELECT
 prefix||value Account,
 count(distinct thirdcolumn) Totals
FROM Transactions
GROUP BY prefix, value

The count(distinct thirdcolumn) says you want a count of the distinct values in the third column. The GROUP BY prefix, value says you want a row returned for each unique prefix/value combination and that the count applies to that combination.

Note that "thirdcolumn" is a placeholder for the name of your third column, not a magic keyword, since I didn't see the actual name in the post.

CodePudding user response：

If you want the number of rows for each prefix/value pair then you can use:

SELECT prefix || value AS account,
       COUNT(*) AS totals
FROM   Transactions
GROUP BY prefix, value

You do not want to count the DISTINCT values for prefix/value as if you GROUP BY those values then different values for the pairs will be in different groups so the COUNT of DISTINCT prefix/value pairs would always be one.