In SQL Server, I have several tables that I need to join together to create one query that looks like this:
Country EmailCount RecordCount PercentRecords
USA 50000 5000 10.00
Brazil 40000 7500 18.75
Germany 30000 25000 83.33
Tables and applicable columns
MailboxSummary Contains Counts by RecordTypes for user mailbox
Count: Count of emails
MailboxFK: Link to MailboxID in the Mailboxes table
RecordTypeFK: Link to RecordTypeID in RecordTypes table
Mailboxes Contains details about each user, including profile (Country)
MailboxID: MailboxID PK
ProfileFK: Link to ProfileID in Profiles table
Profiles Contains details about each profile (Country)
ProfileID: ProfileID PK
Name: Name of Country
RecordTypes Contains details about each RecordType
RecordTypeID: RecordTypeID PK
ZoneFK: Link to ZoneID in Zones table (not listed)
I can get the results easily with two queries:
-- Count by Country
SELECT pf.Name, sum(mbs.Count) As TotalCount
FROM MailboxSummary mbs
INNER JOIN Mailboxes mb ON mbs.MailboxFK = mb.MailboxID
INNER JOIN Profiles pf ON mb.ProfileFK = pf.ProfileID
INNER JOIN RecordTypes rt ON mbs.RecordTypeFK = rt.RecordTypeID
GROUP BY pf.Name
ORDER BY pf.Name
-- RecordCount By Country
SELECT pf.Name, sum(mbs.Count) As TotalRecordCount
FROM MailboxSummary mbs
INNER JOIN Mailboxes mb ON mbs.MailboxFK = mb.MailboxID
INNER JOIN Profiles pf ON mb.ProfileFK = pf.ProfileID
INNER JOIN RecordTypes rt ON mbs.RecordTypeFK = rt.RecordTypeID
WHERE rt.ZoneFk = 3
GROUP BY pf.Name
ORDER BY pf.Name
I can get the desired results by user ID with one query, but not when grouping by Country.
Any ideas?
Thanks!