SQL Query issues - different conditions in the SELECT list

• mmwillingham

  Valued Member

  Points: 64

  More actions

  April 9, 2021 at 3:38 pm

  #3867305

  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!

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  April 9, 2021 at 3:56 pm

  #3867311

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• ratbak

  Default port

  Points: 1407

  More actions

  April 9, 2021 at 4:04 pm

  #3867322

  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...

   

• mmwillingham

  Valued Member

  Points: 64

  More actions

  April 9, 2021 at 7:54 pm

  #3867383

  Thanks! Your answer is perfect.

• mmwillingham

  Valued Member

  Points: 64

  More actions

  April 9, 2021 at 7:54 pm

  #3867384

  Thanks! Your answer is also perfect.