Distinct Counts

[Chris Campbell]

I have a sql expression that returns the number of records that match a condition that exists in the table "envelope"

Select contact.accountname,

(Select
Count(env.fk_campaignname)

FROM ds04.campaignname
INNER JOIN ds04.envelope env ON env.fk_campaignname = campaignname.pklkey
INNER JOIN ds04.pledge plg ON env.envelopekey = plg.fk_envelope
WHERE plg.fk_contact = contact.contactkey

) AS reccount

FROM ds04.contact
WHERE contact.contactkey = 23460
GROUP BY contact.accountname, contact.contactkey

ORDER BY contact.accountname

This works fine except I'd like to change it so that the "Count()" only returns the count of unique keys. So if this account has more than one record for a given env.fk_campaignname I only want it to count as one occurance. So I tried playing with the "Distinct" expression and Group By expressions but I keep getting an error telling me that my sub query is returning multiple rows, which technically is true but I'm trying to put a "count" wrapper around it but to no avail.

The data looks something like this in the envelope table:
Key

fk_contact

fk_campaignname

1

3

4

2

3

4

3

3

5

4

3

5

5

3

6

6

3

6


Currently my query is returning a value of 6 for the Count(fk_campaignname). I want it to return 3. One for each unique fk_campaignname. Seems to me this should be stupid simple but I'm running into a wall. Any assistance would be greatly appreciated

Regards,
Chris Campbell | Software Engineer
C A S C A D E D A T A S O L U T I O N S

[David G. Johnston]

Post by Chris Campbell
Select contact.accountname,
​​
(Select
Count(env.fk_campaignname)
FROM ds04.campaignname
INNER JOIN ds04.envelope env ON env.fk_campaignname =
campaignname.pklkey
INNER JOIN ds04.pledge plg ON env.envelopekey = plg.fk_envelope
WHERE plg.fk_contact = contact.contactkey
) AS reccount
FROM ds04.contact
WHERE contact.contactkey = 23460
GROUP BY contact.accountname, contact.contactkey
ORDER BY contact.accountname
This works fine except I’d like to change it so that the “Count()” only
returns the count of unique keys. So if this account has more than one
record for a given env.fk_campaignname I only want it to count as one
occurance.

​(Select Count( DISTINCT env.fk_campaignname) ...

https://www.postgresql.org/docs/10/static/sql-expressions.html#SYNTAX-AGGREGATES

David J.

[Chris Campbell]

Select contact.accountname,
(Select
Count(env.fk_campaignname)
FROM ds04.campaignname
INNER JOIN ds04.envelope env ON env.fk_campaignname = campaignname.pklkey
INNER JOIN ds04.pledge plg ON env.envelopekey = plg.fk_envelope
WHERE plg.fk_contact = contact.contactkey
) AS reccount
FROM ds04.contact
WHERE contact.contactkey = 23460
GROUP BY contact.accountname, contact.contactkey
ORDER BY contact.accountname

This works fine except I’d like to change it so that the “Count()” only returns the count of unique keys. So if this account has more than one record for a given env.fk_campaignname I only want it to count as one occurance.


​(Select Count( DISTINCT env.fk_campaignname) ...

https://www.postgresql.org/docs/10/static/sql-expressions.html#SYNTAX-AGGREGATES

David J.



Thank you David. That did the trick.

Chris