Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
---
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
---
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
---
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
---
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via SQLMonster.com"
<forum@.nospam.SQLMonster.com> wrote:
Something like:
>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via SQLMonster.com"
<forum@.SQLMonster.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev