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