This could have been achieved by inner joining the query the original result set with a count(*) grouped by partnerid, however the aim was to get the result with a single query.
This was achieved by making the PartnerTotalQty column a SELECT statement which inner joined with the main table in the query.
I'm not sure this is a very efficient way of retrieving this result set, and the inner join may be more suitable for a live environment, however this does provide a somewhat neater solution.
SELECT PartnerID,
PartnerName,
SUBSTRING(email,CHARINDEX('@', email)+1,65)as 'Domain',
(SELECT COUNT(*) FROM counts2.dbo.MOWelAnalysis_pk2 WHERE PartnerID = a.partnerID) AS PartnerTotalQty,
COUNT (*) as QTY,
SUM(CASE WHEN bouncecode is not null THEN 1 ELSE 0 END) AS Bounce,
SUM(CASE WHEN welunsub =1 THEN 1 ELSE 0 END) AS welunsub,
SUM(CASE WHEN JMR = 1 THEN 1 ELSE 0 END) AS JMR,
SUM(CASE WHEN QuesCplted = 1 THEN 1 ELSE 0 END) AS QuesCplted,
SUM(CASE WHEN OpenMail = 1 THEN 1 ELSE 0 END) AS OpenMail,
SUM(CASE WHEN AOLAbuse = 1 THEN 1 ELSE 0 END) AS AOLAbuse
FROM counts2.dbo.MOWelAnalysis_pk2 a
GROUP BY PartnerID,
PartnerName,
SUBSTRING(email,CHARINDEX('@', email)+1,65)
ORDER BY PartnerName
PartnerName,
SUBSTRING(email,CHARINDEX('@', email)+1,65)as 'Domain',
(SELECT COUNT(*) FROM counts2.dbo.MOWelAnalysis_pk2 WHERE PartnerID = a.partnerID) AS PartnerTotalQty,
COUNT (*) as QTY,
SUM(CASE WHEN bouncecode is not null THEN 1 ELSE 0 END) AS Bounce,
SUM(CASE WHEN welunsub =1 THEN 1 ELSE 0 END) AS welunsub,
SUM(CASE WHEN JMR = 1 THEN 1 ELSE 0 END) AS JMR,
SUM(CASE WHEN QuesCplted = 1 THEN 1 ELSE 0 END) AS QuesCplted,
SUM(CASE WHEN OpenMail = 1 THEN 1 ELSE 0 END) AS OpenMail,
SUM(CASE WHEN AOLAbuse = 1 THEN 1 ELSE 0 END) AS AOLAbuse
FROM counts2.dbo.MOWelAnalysis_pk2 a
GROUP BY PartnerID,
PartnerName,
SUBSTRING(email,CHARINDEX('@', email)+1,65)
ORDER BY PartnerName
| PartnerID | PartnerName | PartnerTotalQty | Domain | QTY | Bounce | welunsub | JMR | QuesCplted | OpenMail | AOLAbuse |
| 1581 | Adsmarket | 20 | prontomail.com | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
| 1581 | Adsmarket | 20 | myway.com | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
| 1581 | Adsmarket | 20 | aber.ac.uk | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1581 | Adsmarket | 20 | trentonbox.co.uk | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1581 | Adsmarket | 20 | definitespace.co.uk | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1581 | Adsmarket | 20 | gmail.com | 15 | 2 | 0 | 0 | 12 | 0 | 0 |
| 1456 | AllTheJobs | 2 | unal.edu.co | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1456 | AllTheJobs | 2 | blueyonder.co.uk | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1396 | AOL | 15 | hotmail.fr | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1396 | AOL | 15 | tubetorque.co.uk | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1396 | AOL | 15 | al.com | 8 | 8 | 0 | 0 | 4 | 0 | 0 |
| 1396 | AOL | 15 | googlemail.com | 3 | 0 | 0 | 0 | 2 | 0 | 0 |
| 1396 | AOL | 15 | lymmhs.co.uk | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| 1396 | AOL | 15 | bone.co.uk | 1 | 0 | 0 | 0 | 1 | 1 | 0 |