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:

Show quoteHide quoteand 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"
<fo***@SQLMonster.com> wrote:
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

StoredProc Help...
The server encountered a stack overflow during compile time.
SP4 reports queries blocking on themselves?
Return a horizontal table for result
comparing text fields - Second try