SQL Query issues - different conditions in the SELECT list

Question

SQL Query issues - different conditions in the SELECT list

mmwillingham 1

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!

Yitzhak Khabinsky 26,586 Reputation points

2021-04-09T15:24:14.72+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
EchoLiu-MSFT 14,621 Reputation points

2021-04-16T06:23:21.01+00:00

Do you have anyupdate?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

1 answer

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2021-04-09T15:24:14.72+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
EchoLiu-MSFT 14,621 Reputation points

2021-04-16T06:23:21.01+00:00

Do you have anyupdate?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Answer 1

EchoLiu-MSFT 14,621

Hi @mmwillingham ,

Welcome to the microsoft TSQL Q&A forum!Your question involves tsql, my colleague helped you add the tsql tag.

If you use user-id and country as the grouping conditions to get different results, I guess it may be that one user id corresponds to multiple countries,the country and user id should not correspond one-to-one.But since you did not provide specific data, these are just guesses.

If this does not solve your problem,please us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result. So that we’ll get a right direction and make some test.

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

mmwillingham 1 Reputation point

2021-04-16T19:27:42.34+00:00

Hi guys,
Thanks for your help.

I was able to solve the issue by putting the conditions in a CASE:

SELECT pf.Name
,TotalCount = FORMAT(SUM(CASE WHEN rt.ZoneFk IS NOT NULL THEN Count ELSE 0 END),''N0'')
,TotalRecordCount = FORMAT(SUM(CASE WHEN rt.ZoneFk = 3 THEN mbs.Count ELSE 0 END),''N0'')
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

Thanks again!
Martin
Viorel 122.6K Reputation points

2021-04-16T19:34:58.477+00:00

Because you have WHERE rt.ZoneFk = 3 do you really need WHEN rt.ZoneFk IS NOT NULL and WHEN rt.ZoneFk = 3?
mmwillingham 1 Reputation point

2021-04-16T20:55:58.56+00:00

In the 1st case, I don't need any conditions, but wasn't sure of a better syntax for it.
In the 2nd case, I do need WHEN rt.ZoneFk = 3

thanks

EchoLiu-MSFT 14,621

I am glad to hear that your problem has been resolved. This is a simple query, it seems that there is no easier way, but iif can also be used:

SELECT pf.Name
,TotalCount = FORMAT(SUM(IIF(rt.ZoneFk IS NOT NULL,[Count],0)),''N0'')
,TotalRecordCount = FORMAT(SUM(IIF(rt.ZoneFk = 3,mbs.Count,0)),''N0'')
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

Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo

Share via

SQL Query issues - different conditions in the SELECT list

1 answer

Your answer