All geek questions in one place

How to combine SQL queries with different expressions?

I have three queries that are already at the peak of my knowledge of SQL (if that matters for Microsoft SQL 2005), and now I need to combine them into one query with all the values ​​in one row.

My actual queries are below, but I thought it would be easier if I provided a simple version here:

Request one:

-- Provides School District summary based on a CountyID
SELECT DistrictID, Count(Schools) as NumberofSchools
FROM Schools
WHERE (CountyID = 207)
GROUP BY DistrictID

Query output:

DistrictID  |  NumberofSchools
345         |  26
567         |  17
211         |  9

Request Two:

-- Summarizes Activity from our Contact Manager (GoldMine)
SELECT DistrictID, Count(Contacts) as NumberofContacts, MAX(Contact) as LastActivity
FROM ContactManager JOINED WITH CONTACT MANAGER TABLES
WHERE (CountyID = 207)
GROUP BY DistrictID

Query output of two samples:

DistrictID  |  NumberofContacts  |  LastActivity
345         |  29                |  Nov 12, 2010
567         |  31                |  Dec 5, 2010
211         |  4                 |  Oct 9, 2010

Request Triangle:

-- Summarizes data from our Opt-In Email Newsletter
SELECT DistrictID, Count(EmailSubscribers) AS NumberofSubscribers, MAX(Date) AS LastSent
FROM SubscribeList JOINED WITH Schools Tables
WHERE (CountyID = 207)
GROUP BY DistrictID

Output with three queries:

DistrictID  |  NumberofSubscribers  |  LastSent
345         |  2                    |  Sep 4, 2010
567         |  3                    |  Oct 22, 2010
211         |  1                    |  NULL

I tried to make a huge UNION of them with the parent SELECT statement (the following data is from this website and by entering SELECT NULL AS MissingColumnName for each data set), but it is really ugly - and does not return everything in one row.

I am looking for the results as follows:

DistrictID  |  NumberofSchools  |  NumberofContacts  |  LastActivity  |  NumberofSubscribers  |  LastSent
345         |  26               |  29                |  Nov 12, 2010  |  2                     |  Sep 4, 2010
567         |  17               |  31                |  Dec 5, 2010   |  3                     |  Oct 22, 2010 
211         |  9                |  4                 |  Oct 9, 2010   |  1                     |  NULL

? ( , , , )

!

, - , . ( - SQL, .): -)

:

SELECT
    institutionswithzipcodesadditional_1.DistrictID, institutionswithzipcodesadditional_1.InstitutionName, institutionswithzipcodesadditional_1.Latitude, institutionswithzipcodesadditional_1.Longitude,
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (4, 5, 6, 7, 8, 14, 15, 16, 20) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS OthersEnrollment,
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (4, 5, 6, 7, 8, 14, 15, 16, 20) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS OthersCount, 
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (13) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS K12SchoolsEnrollment, 
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (13) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS K12SchoolsCount, 
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (12) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS HighSchoolsEnrollment, 
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (12) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS HighSchoolsCount, 
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (10, 11) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS MiddleSchoolsEnrollment,
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (10, 11) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS MiddleSchoolsCount,
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (9) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS ElementariesEnrollment,
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (9) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS ElementariesCount,
    SUM(CASE WHEN institutionswithzipcodesadditional.LevelID IN (4, 5, 6, 7, 8, 14, 15, 16, 20, 13, 12, 10, 11, 9) THEN institutionswithzipcodesadditional.Enrollment ELSE 0 END) AS AllSchoolsEnrollment, 
    COUNT(CASE WHEN institutionswithzipcodesadditional.LevelID IN (4, 5, 6, 7, 8, 14, 15, 16, 20, 13, 12, 10, 11, 9) THEN institutionswithzipcodesadditional.InstitutionID ELSE NULL END) AS AllSchoolsCount
FROM zipcodes 
    INNER JOIN users_link_territory ON zipcodes.CountyID = users_link_territory.CountyID
    INNER JOIN institutionswithzipcodesadditional ON zipcodes.ZIP = institutionswithzipcodesadditional.ZIP
    RIGHT OUTER JOIN institutionswithzipcodesadditional AS institutionswithzipcodesadditional_1 ON institutionswithzipcodesadditional.DistrictID = institutionswithzipcodesadditional_1.InstitutionID
WHERE
    (institutionswithzipcodesadditional_1.CountyID = 207)
AND (institutionswithzipcodesadditional_1.LevelID IN (1, 2, 3, 6, 7, 8))
GROUP BY institutionswithzipcodesadditional_1.DistrictID, institutionswithzipcodesadditional_1.InstitutionName, institutionswithzipcodesadditional_1.Latitude, institutionswithzipcodesadditional_1.Longitude

:

SELECT
institutionswithzipcodesadditional_1.InstitutionID AS DistrictID,
COUNT(GoldMine.dbo.CONTACT1.ACCOUNTNO) AS GM,
MAX(CASE WHEN GoldMine.dbo.CONTHIST.USERID NOT IN ('DEBRA', 'TRISH', 'RUSSELL', 'GREG') THEN GoldMine.dbo.CONTHIST.OnDate ELSE NULL END) AS LastActivity
FROM institutionswithzipcodesadditional
    LEFT OUTER JOIN contacts
    LEFT OUTER JOIN GoldMine.dbo.CONTACT1
    RIGHT OUTER JOIN GoldMine_Link_Russell 
    ON GoldMine.dbo.CONTACT1.KEY3 = GoldMine_Link_Russell.GoldMineKeyThree
    ON contacts.ContactID = GoldMine_Link_Russell.ContactID 
    ON institutionswithzipcodesadditional.InstitutionID = contacts.InstitutionID
    RIGHT OUTER JOIN institutionswithzipcodesadditional AS institutionswithzipcodesadditional_1
    ON institutionswithzipcodesadditional.DistrictID = institutionswithzipcodesadditional_1.InstitutionID
    LEFT OUTER JOIN GoldMine.dbo.CONTHIST ON GoldMine.dbo.CONTHIST.ACCOUNTNO = GoldMine.dbo.CONTACT1.ACCOUNTNO
WHERE (institutionswithzipcodesadditional_1.CountyID = 207) AND (institutionswithzipcodesadditional_1.LevelID IN (1, 2, 3, 6, 7, 8))
GROUP BY institutionswithzipcodesadditional_1.InstitutionID

:

SELECT
COUNT(NewsletterContacts.Email) AS EMailableContacts,
institutionswithzipcodesadditional_1.InstitutionID AS DistrictID,
MAX(newsletterregister.Sent) AS LastSent
FROM newsletterregister
    RIGHT OUTER JOIN contacts ON newsletterregister.ContactID = contacts.ContactID
    RIGHT OUTER JOIN institutionswithzipcodesadditional ON contacts.InstitutionID = institutionswithzipcodesadditional.InstitutionID
    LEFT OUTER JOIN EmailableContacts ON institutionswithzipcodesadditional.InstitutionID = EmailableContacts.InstitutionID
    RIGHT OUTER JOIN institutionswithzipcodesadditional AS institutionswithzipcodesadditional_1 ON 
    institutionswithzipcodesadditional.DistrictID = institutionswithzipcodesadditional_1.InstitutionID
WHERE
    (institutionswithzipcodesadditional_1.CountyID = 207)
    AND (institutionswithzipcodesadditional_1.LevelID IN (1, 2, 3, 6, 7, 8))
GROUP BY institutionswithzipcodesadditional_1.InstitutionID
+3
3

Mark, . , , select. , , temp , , , / .

With SomeGoodName as
(
  SELECT ...
)
,
AnotherDescriptiveName as
(
  Select ...
)
,
AThirdNiceName as
(
  Select ...
)
SELECT
    T1.DistrictID,
    T1.NumberofSchools,
    T2.NumberofContacts,
    T2.LastActivity,
    T3.NumberofSubscribers,
    T3.LastSent
FROM SomeGoodName T1
JOIN AnotherDescriptiveName T2 ON T1.DistrictID = T2.DistrictID
JOIN AThirdNiceName T3 ON T1.DistrictID = T3.DistrictID
+1

, :

SELECT
    T1.DistrictID,
    T1.NumberofSchools,
    T2.NumberofContacts,
    T2.LastActivity,
    T3.NumberofSubscribers,
    T3.LastSent
FROM (SELECT ...) T1
JOIN (SELECT ...) T2 ON T1.DistrictID = T2.DistrictID
JOIN (SELECT ...) T3 ON T1.DistrictID = T3.DistrictID

(SELECT ...) . , OUTER JOINs, , .. , .

+4

, , , "" , , , .

, , temp, .

, , , , .

, , , , .

0

Source: https://habr.com/ru/post/1782660/

More articles:


All Articles