April 9, 2021 at 3:38 pm
If this is the wrong forum, please let me know.
In SQL Server 2017, 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!
April 9, 2021 at 3:56 pm
There is a T-SQL forum. This type of q would generally go there. But not a real problem. Try the code below:
SELECT pf.Name,
sum(mbs.Count) As TotalCount,
sum(case when rt.ZoneFk = 3 then mbs.Count else 0 end) As TotalRecordCount,
--calc with sum() / sum() to get the % you want
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
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 9, 2021 at 4:04 pm
https://www.sqlservercentral.com/forums/forum/sql-server-2017/sql-server-2017-development is probably more relevant... but close enough.
You can probably use something like
sum(CASE WHEN rt.ZoneFk = 3 THEN mbs.Count ELSE 0 END)
for the filtered TotalRecordCount.
Beat to the punch by Scott...
April 9, 2021 at 7:54 pm
Thanks! Your answer is perfect.
April 9, 2021 at 7:54 pm
Thanks! Your answer is also perfect.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply