September 2, 2003 at 12:03 pm
I want to return a recordset with 2 columns "media_cost" and "production_cost"
I also want to return the sum of all of the "media_cost" values
here are my records:
record_id | media _cost | production_cost
------------------------------------------------------
1 | 14 | 12
2 | 11 | 17
3 | 7 | 10
here is my intended results:
media _cost | production_cost | thesum
--------------------------------------------------------------------------
14 | 12 | 32
11 | 17 | 32
7 | 10 | 32
here is the sql statement that I tried (BUT IT FAILS)
i get an error message about "aggregate clause" (too long to type in entirity)
SELECT
dbo.advertisements.media_cost,
dbo.advertisements.production_cost,
sum(dbo.advertisements.media_cost) as thesum
FROM dbo.advertisements
Alternatively , it would be OK to return thesum in a 2nd recordset after the first one
Anyone got any ideas
thanks in advance
September 2, 2003 at 12:35 pm
The message about "aggregate.." means that you are including a SUM(someColumn) along with other columns without specifying a GROUP BY clause.
--
However, this is a perfect case for another resultset. You will simply be repeating the value of SUM(mediacost) over and over again. So simply do this:
SELECT record_id, media_cost, production_cost
FROM advertisements
ORDER BY record_id
GO
SELECT SUM(media_cost) AS "TotalMediaCost" FROM advertisements
GO
Edited by - jpipes on 09/02/2003 12:35:53 PM
September 3, 2003 at 1:43 am
Correct. Should be in two recordsets. If for some reason, you really need it in a single set, try the following statement.
SELECT media_cost, production_cost,
(SELECT SUM(media_cost)
FROM advertisements) as thesum
FROM advertisements
September 3, 2003 at 2:53 am
thank you both for excellent answers.
However where it fails though, is because I have to repeat the FROM clause. In the above example I really simplified my FROM clause. In the actual case there is a lot of joins. The WHERE clause is really complicated also.
Is there a solution where I dont have to repeat the FROM and WHERE clause.
Edited by - eamonroche on 09/03/2003 02:53:13 AM
September 3, 2003 at 6:37 am
Well, hard to tell what's best, if I don't know the database structure, number of records etc. But if your main concern is not to repeat the FROM and WHERE clauses, you could store the result in a table and then query this result table to obtain "thesum"...
It remains to be seen whether it is better than to repeat the select on actual data, though. I'm not sure why you are trying to avoid repeating the FROM and WHERE... is it really that terrible?
September 3, 2003 at 6:52 am
Go ahead and post your real script, and we'll take a look.
September 3, 2003 at 7:36 am
i think that I sorted the problem [see script below] by using 2 select statements: a. SELECT INTO a temp table
b. doing a SUM on the temp table
if there is a better way let me know. thanks.
CREATE Procedure HEBE_Report
/*
-by E.R. on Sept 2 '03
*/
(
@startdate datetime,
@enddate datetime,
@org_id int = null,
@pub_id int = null,
@media_size_id int = null,
@cat_id int = null,
@sub_cat_id int = null
)
As
set nocount on
declare @media_costs_all_positions money
declare @production_cost_per_position money
declare @total_cost money
declare @vcTitle varchar(250)
SELECT
dbo.advertisements.actual_media_costs_all_positions as mediacostall,
dbo.advertisements.actual_production_cost_per_position as prodcosteach,
dbo.advertisements.date_of_publication as datepub,
(
select count(*) from positions
where
-- filter by org_id, cat_id and sub_cat_id
dbo.positions.fk_healthboard_id = coalesce(@org_id, dbo.positions.fk_healthboard_id)
AND
dbo.positions.position_cat_id = coalesce(@cat_id, dbo.positions.position_cat_id)
AND
dbo.positions.position_sub_cat_id = coalesce(@sub_cat_id, dbo.positions.position_sub_cat_id)
) as poscount,
dbo.publication_media_sizes.media_type_name as mediatypename,
dbo.publications.publication_name as pubname
-- note: the category and sub-category appear as both a ref to the position_categories table and also as a text version in the positions table
into #myreporttable -- ie put into a temporary table which will be read later on
FROM dbo.publication_media_sizes INNER JOIN
dbo.publications ON dbo.publication_media_sizes.fk_pub_id = dbo.publications.publication_id RIGHT OUTER JOIN
dbo.advertisements ON dbo.publication_media_sizes.pub_media_type_id = dbo.advertisements.fk_publication_media_type_id
WHERE
-- filter by pub_id, media_size_id, startdate , enddate
dbo.publication_media_sizes.fk_pub_id = coalesce(@pub_id, dbo.publication_media_sizes.fk_pub_id)
AND
dbo.publication_media_sizes.pub_media_type_id = coalesce(@media_size_id, dbo.publication_media_sizes.pub_media_type_id)
AND
dbo.advertisements.date_of_publication>=@startdate
AND
dbo.advertisements.date_of_publication<=@enddate
-- HERE IS WHERE THE RESULTS ARE ACTUALLY RETURNED
select *, poscount * prodcosteach as totprodcost, mediacostall + (poscount * prodcosteach) as totaladcost from #myreporttable order by datepub
select sum(mediacostall + (poscount * prodcosteach)) as sum_totaladcost from #myreporttable
drop table #myreporttable
set nocount off
return
GO
September 3, 2003 at 7:59 am
eamonroche,
your proc looks ok. you may run into some performance problems further down the road if you're dealing with extremely high vlume datasets, but for now, everything looks cool. If this procedure is run often, you will likely see a high recompile rate for the procedure, or a bad query plan being used since you are creating, selecting from, and destroying a temp table all within a single proc. If you do see performance problems, you can always create the temp table outside of this procedure to alleviate the recompiles...
--
Good job,
Jay
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply