How to get a count of unique account numbers per invoice-CodePudding

I am trying to create a field in my query that grabs a count of unique account numbers for a particular invoice

My current query:

SELECT 
    gl.source,
    COUNT(gl.account_number) as 'Count of Invoices',
    gl.account_number
FROM 
    gl
WHERE 
    gl.source = *invoice number*
GROUP BY 
    gl.source, gl.account_number

Currently this only returns "1" for any invoice in the column. I have one invoice that has 5 different account_numbers. I want the 'Count of Invoices" to show 5 instead of "1" for that order. How can I achieve that?

CodePudding user response：

I think you mean you want "count of accounts", not "count of invoices"?

If you want distinct invoices you can just remove account_number from your query.

However, if you want to keep account number in the results then you can use COUNT as a window function and partition by source.

DECLARE @t TABLE (source VARCHAR(10),
                  account_number VARCHAR(10));
INSERT @t (source, account_number)
VALUES ('inv1', 'acct1'),
       ('inv2', 'acct1'),
       ('inv2', 'acct2'),
       ('inv2', 'acct3'),
       ('inv2', 'acct4'),
       ('inv2', 'acct5');

SELECT source,
       COUNT(account_number) [Count of Invoices]
FROM @t
GROUP BY source;

SELECT source,
       COUNT(account_number) OVER (PARTITION BY source ORDER BY source),
       account_number
FROM @t
GROUP BY source, account_number;

The first query produces

source  |  Cout of Invoices
-------- ------------------
 inv1   |        1
 inv2   |        5

Second query with the windowing produces

source  |  Cout of Invoices  |  account_number
-------- -------------------- ------------------
 inv1   |        1           |     acct1
 inv2   |        5           |     acct1
 inv2   |        5           |     acct2
 inv2   |        5           |     acct3
 inv2   |        5           |     acct4
 inv2   |        5           |     acct5

CodePudding user response：

You would need to remove gl.account_number from your SELECT and GROUP BY, as this is causing the Count of Invoices to be broken up among the different account numbers.