Merging Results/ UNION problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Merging Results/ UNION problem

Author

Topic

Mari
Starting Member

19 Posts

Posted - 2007-09-14 : 11:08:19

Hi Everyone,

I have a new problem, I don't know how to make my results merge by country. I've used the UNION ALL but it still separates the countries by statemen, can you please help me.

here is my sql and results below: as you can see results give me for example 2 Algerie, 2 Bahrain etc. I need to merge/sum those 2 numbers from both statements by country.

select [Year],Country,
'Total'=SUM(CASE when CorrespondenceType in ('UA','AL','GOV') THEN 1 ELSE 0 END),
'Total_Comm'=SUM(CASE when CorrespondenceType in ('UA','AL') THEN 1 ELSE 0 END),
'Total_Gov'=SUM(CASE CorrespondenceType WHEN 'GOV' THEN 1 ELSE 0 END)
from #Case,AAD,Country
where #Case.caseid=AAD.missingpersonid
and AAD.AADCountryID=Country.CountryID
and aadid=1
group by [year],Country
UNION ALL
select [Year],Country,
'Total'=SUM(CASE when CorrespondenceType in ('UA','JUA','AL','JAL','GOV') THEN 1 ELSE 0 END),
'Total_Comm'=SUM(CASE when CorrespondenceType in ('UA','JUA','AL','JAL') THEN 1 ELSE 0 END),
'Total_Gov'=SUM(CASE CorrespondenceType WHEN 'GOV' THEN 1 ELSE 0 END)
from #Case,Mandate_Case,Country
where #Case.caseid=Mandate_Case.caseid and Mandate_Case.CaseCountryID=Country.CountryID
group by [year],Country
order by country

YEAR COUNTRY TOTAL TOTAL_COMM TOTAL_GOV
2007 Afghanistan 2 2 0
2007 Algérie 9 9 0
2007 Algérie 12 7 5
2007 Angola 4 3 1
2007 Argentina 14 10 4
2007 Armenia 1 1 0
2007 Austria 1 1 0
2007 Azerbaijan 3 3 0
2007 Bahrain 2 1 1
2007 Bahrain 9 6 3
2007 Bangladesh 1 0 1
2007 Bangladesh 36 19 17

I would be so happy if someone can help me, been trying all sorts, Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-14 : 11:40:18

use UNION instead of UNION ALL

union eliminates duplicates

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 11:48:44

yes, but last three colunms are not distinct.

SELECT year, country, sum(total) as total, sum(total_comm) as total_comm, sum(total_gov) As total_gov
from (
.......... your query above here
) As d
group by year, country

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 11:53:16

[code]select year,
country,
sum(total) as total,
sum(total_comm) as total_comm,
sum(total_gov) as total_gov
FROM (
select [Year],
Country,
'Total' = 1,
'Total_Comm'=CASE when CorrespondenceType in ('UA','AL') THEN 1 ELSE 0 END,
'Total_Gov'=CASE CorrespondenceType WHEN 'GOV' THEN 1 ELSE 0 END
from #Case
inner join AAD on #Case.caseid=AAD.missingpersonid
inner join Country on AAD.AADCountryID=Country.CountryID
where aadid = 1
and CorrespondenceType in ('UA','AL','GOV')

UNION ALL

select [Year],
Country,
'Total' = 1,
'Total_Comm'=CASE when CorrespondenceType in ('UA','JUA','AL','JAL') THEN 1 ELSE 0 END,
'Total_Gov'=CASE CorrespondenceType WHEN 'GOV' THEN 1 ELSE 0 END
from #Case
inner join Mandate_Case on #Case.caseid=Mandate_Case.caseid
inner join Country on Mandate_Case.CaseCountryID=Country.CountryID
where CorrespondenceType in ('UA','JUA','AL','JAL','GOV')
) AS d
group by year,
country
order by country,
year desc[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 12:24:55

And if you are interested in further optimization, try this

SELECT		{Table name alias here}.[Year],
		{Table name alias here}.Country, 
		COUNT(*) AS Total,
		SUM(CASE
			WHEN {Table name alias here}.CorrespondenceType IN ('UA', 'AL', 'JUA', 'JAL') THEN 1
			ELSE 0
		END) AS Total_Comm,
		SUM(CASE
			WHEN {Table name alias here}.CorrespondenceType = 'GOV' THEN 1
			ELSE 0
		END) AS Total_Gov
FROM		#Case AS s
INNER JOIN	(
			SELECT	MissingPersonID AS theCaseID,
				AADCountryID AS theCountryID
			FROM	AAD
			WHERE	AadID = 1

			UNION ALL

			SELECT	CaseID,
				CaseCountryID
			FROM	Mandate_Case
		) AS x ON x.theCaseID = c.CaseID
INNER JOIN	Country AS c ON c.CountryID = x.theCountryID
WHERE		{Table name alias here}.CorrespondenceType IN ('UA', 'AL', 'GOV', 'JUA', 'JAL')
GROUP BY	{Table name alias here}.[Year],
		{Table name alias here}.Country
ORDER BY	{Table name alias here}.Country,
		{Table name alias here}.[Year] DESC

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 12:27:16

I gave you a good advice here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88692
to ALWAYS prefix your columns when having more than one table in a query.
Please follow this advice and you will not get headaches in the future when truying to debog your queries.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 12:38:45

If you succeed in the query posted 09/14/2007 : 12:24:55
you can look forward to drop about 30-35% of the reads, and to a 50% speed improvement!

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-09-17 : 05:22:54

Hello Peso,

Thank you so much, the query posted 09/14/2007 : 11:53:16 worked marvelously. As advised, I've been trying the 09/14/2007 : 12:24:55 query and I keep getting error 'the column prefix 'c' does not match with a table name or alias name used in the query'

So, I guess I will ahve to settle for first query that works!

Thanks a million again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 06:08:47

No, don't settle for crap code.
Work with the query until it works with accurate table prefix.

You will gain with this now and in the future.

SELECT		{Table name alias here}.[Year],
		{Table name alias here}.Country, 
		COUNT(*) AS Total,
		SUM(CASE
			WHEN {Table name alias here}.CorrespondenceType IN ('UA', 'AL', 'JUA', 'JAL') THEN 1
			ELSE 0
		END) AS Total_Comm,
		SUM(CASE
			WHEN {Table name alias here}.CorrespondenceType = 'GOV' THEN 1
			ELSE 0
		END) AS Total_Gov
FROM		#Case AS s
INNER JOIN	(
			SELECT	MissingPersonID AS theCaseID,
				AADCountryID AS theCountryID
			FROM	AAD
			WHERE	AadID = 1

			UNION ALL

			SELECT	CaseID,
				CaseCountryID
			FROM	Mandate_Case
		) AS x ON x.theCaseID = s.CaseID
INNER JOIN	Country AS c ON c.CountryID = x.theCountryID
WHERE		{Table name alias here}.CorrespondenceType IN ('UA', 'AL', 'GOV', 'JUA', 'JAL')
GROUP BY	{Table name alias here}.[Year],
		{Table name alias here}.Country
ORDER BY	{Table name alias here}.Country,
		{Table name alias here}.[Year] DESC

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-09-17 : 09:24:39

Great! it works! Thanks again,Peso, and I will read thru it again to understand this technique and use it more in the future, it's much much shorter!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 09:46:43

Because of you not having prefixed the columns with tablenames and/or table name alias, the query above might not show exact same amount of records.
For instance, the "WHERE AadID = 1" might be misplaced.

Could you do us a favor, and post back this query with correcte table name or table name alias prefixes?

SELECT		{Table name alias here}.[Year],
		{Table name alias here}.Country, 
		COUNT(*) AS Total,
		SUM(CASE
			WHEN {Table name alias here}.CorrespondenceType IN ('UA', 'AL', 'JUA', 'JAL') THEN 1
			ELSE 0
		END) AS Total_Comm,
		SUM(CASE
			WHEN {Table name alias here}.CorrespondenceType = 'GOV' THEN 1
			ELSE 0
		END) AS Total_Gov
FROM		#Case AS s
INNER JOIN	(
			SELECT	MissingPersonID AS theCaseID,
				AADCountryID AS theCountryID
			FROM	AAD
			WHERE	{Table name alias here}.AadID = 1

			UNION ALL

			SELECT	CaseID,
				CaseCountryID
			FROM	Mandate_Case
		) AS x ON x.theCaseID = s.CaseID
INNER JOIN	Country AS c ON c.CountryID = x.theCountryID
WHERE		{Table name alias here}.CorrespondenceType IN ('UA', 'AL', 'GOV', 'JUA', 'JAL')
GROUP BY	{Table name alias here}.[Year],
		{Table name alias here}.Country
ORDER BY	{Table name alias here}.Country,
		{Table name alias here}.[Year] DESC

Not only is the code shorter, it should run faster too, because you have one-third less reads...

E 12°55'05.25"
N 56°04'39.16"

Mari
Starting Member

19 Posts

Posted - 2007-09-20 : 06:34:12

Hi Peso,

Sorry for the delayed reply, too much work. Anyway,thanks, getting back to you on this coz saw your message just today. I am posting this as you requested. It works but I have a problem with some numbers that are overlapping between the Mandate_Case.caseid and the AADID as TheCaseID found in the #case table. How do I avoid this? It seems that some case numbers existing in the Mandate_Case table exist also in the AAD table , so it counts them again for the countries.

SELECT #Case.[Year],
Country.Country,
COUNT(*) AS Total,
SUM(CASE
WHEN #Case.CorrespondenceType IN ('UA', 'AL', 'JUA', 'JAL') THEN 1
ELSE 0
END) AS Total_Comm,
SUM(CASE
WHEN #Case.CorrespondenceType = 'GOV' THEN 1
ELSE 0
END) AS Total_Gov
FROM #Case
INNER JOIN (
SELECT MissingPersonID AS theCaseID,
AADCountryID AS theCountryID
FROM AAD
WHERE AAD.AadID = 1

UNION ALL

SELECT CaseID,
CaseCountryID
FROM Mandate_Case
) AS x
ON x.theCaseID = #Case.CaseID
INNER JOIN Country ON Country.CountryID = x.theCountryID
WHERE #Case.CorrespondenceType IN ('UA', 'AL', 'GOV', 'JUA', 'JAL')
GROUP BY #Case.[Year],
Country.Country
ORDER BY Country.Country,
#Case.[Year] DESC

Thanks again and hope you can give me some tips again, been tryin gto work out the numbers , can't seem to get around them as my Temporary table #C has the correct numbers. For example: Total_Gov should be 289 (as in #C table)but it gives me 307 coz it's adding same existing 19 nos. in AAD to Mandate_Case table.

Mari
Starting Member

19 Posts

Posted - 2007-09-20 : 08:51:34

Sorry , thanks, found it, it was actually so stupid of me not to have found that myself. I just keep on finding complicated ways to do it and finally fnd the simplest way when all is exhausted!

SELECT #Case.[Year],
Country.Country,
COUNT(*) AS Total,
SUM(CASE
WHEN #Case.CorrespondenceType IN ('UA', 'AL', 'JUA', 'JAL') THEN 1
ELSE 0
END) AS Total_Comm,
SUM(CASE
WHEN #Case.CorrespondenceType = 'GOV' THEN 1
ELSE 0
END) AS Total_Gov
FROM #Case
INNER JOIN (
SELECT MissingPersonID AS theCaseID,
AADCountryID AS theCountryID
FROM AAD
WHERE AAD.AadID = 1
and AAD.AADID not in (select MissingpersonID from Mandate_case)

UNION ALL

SELECT CaseID,
CaseCountryID
FROM Mandate_Case
WHERE Mandate_Case.Caseid not in (select MissingpersonID from AAD)
) AS x
ON x.theCaseID = #Case.CaseID
INNER JOIN Country ON Country.CountryID = x.theCountryID
WHERE #Case.CorrespondenceType IN ('UA', 'AL', 'GOV', 'JUA', 'JAL')
GROUP BY #Case.[Year],
Country.Country
ORDER BY Country.Country,
#Case.[Year] DESC

Thanks again for your suggestions!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources