Where are you? Where are you?

Question

Post reply

Where are you? Where are you?

eamonroche

SSCommitted

Points: 1678
More actions
September 4, 2003 at 10:02 am

#176330

The sql statement below works fine. However as you can see the exact same WHERE clause is repeated twice within the statement. This seems inefficient. Is there a way of writing this statement withiut repeating the WHERE clause?
Thanks in advance.
SELECT
dbo.organisations.organisation_shortname as org_shortname,
(select sum(media_cost_per_pos)
from dbo.qry_positions_with_costs
WHERE
dbo.qry_positions_with_costs.pub_id = coalesce(@pub_id, dbo.qry_positions_with_costs.pub_id)
AND
dbo.qry_positions_with_costs.media_type_id = coalesce(@media_size_id, dbo.qry_positions_with_costs.media_type_id)
AND
dbo.qry_positions_with_costs.datepub>=@startdate
AND
dbo.qry_positions_with_costs.datepub<=@enddate
AND
dbo.qry_positions_with_costs.cat_id = coalesce(@cat_id, dbo.qry_positions_with_costs.cat_id)
AND
dbo.qry_positions_with_costs.sub_cat_id = coalesce(@sub_cat_id, dbo.qry_positions_with_costs.sub_cat_id)
AND
dbo.qry_positions_with_costs.health_board_id = dbo.organisations.organisation_id
) as tot_media_cost,
(select sum(prod_cost_per_pos
from dbo.qry_positions_with_costs
WHERE
dbo.qry_positions_with_costs.pub_id = coalesce(@pub_id, dbo.qry_positions_with_costs.pub_id)
AND
dbo.qry_positions_with_costs.media_type_id = coalesce(@media_size_id, dbo.qry_positions_with_costs.media_type_id)
AND
dbo.qry_positions_with_costs.datepub>=@startdate
AND
dbo.qry_positions_with_costs.datepub<=@enddate
AND
dbo.qry_positions_with_costs.cat_id = coalesce(@cat_id, dbo.qry_positions_with_costs.cat_id)
AND
dbo.qry_positions_with_costs.sub_cat_id = coalesce(@sub_cat_id, dbo.qry_positions_with_costs.sub_cat_id)
AND
dbo.qry_positions_with_costs.health_board_id = dbo.organisations.organisation_id
) as tot_prod_cost
FROM dbo.organisations
Edited by - eamonroche on 09/04/2003 10:03:23 AM
Edited by - eamonroche on 09/04/2003 10:04:36 AM
Edited by - eamonroche on 09/04/2003 10:05:13 AM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Have you tried this:

SELECT

dbo.organisations.organisation_shortname as org_shortname,

sum(dbo.qry_positions_with_costs.media_cost_per_pos) as tot_media_cost,

sum(dbo.qry_positions_with_costs.prod_cost_per_pos) as tot_prod_cost

from

dbo.organisations, dbo.qry_positions_with_costs

-SQLBill

eamonroche SSCommitted Points: 1678 More actions · Answer 2

???

but you just dropped both WHERE clauses

???

I think I need the where criteria

-- Are saying to put the WHERE claus at the end ie:

SELECT

dbo.organisations.organisation_shortname as org_shortname,

sum(dbo.qry_positions_with_costs.media_cost_per_pos) as tot_media_cost,

sum(dbo.qry_positions_with_costs.prod_cost_per_pos) as tot_prod_cost

from

dbo.organisations,

dbo.qry_positions_with_costs WHERE ........

If this is what you are saying then I cant see how this will work because the last part of the WHERE clause references dbo.organisations.organisation_id

gljjr SSChampion Points: 10080 More actions · Answer 3

Without having a schema with sample data to see how the join might work you could probably rewrite this as follows.



SELECT 

    o.organisation_shortname as org_shortname,

    sum(c.media_cost_per_pos) as tot_media_cost,

    sum(c.prod_cost_per_pos) as tot_prod_cost

FROM dbo.organisations o, dbo.qry_positions_with_costs c     

WHERE c.pub_id = coalesce(@pub_id, c.pub_id)

    AND c.media_type_id = coalesce(@media_size_id, c.media_type_id)

    AND c.datepub >= @startdate

    AND c.datepub <= @enddate

    AND c.cat_id = coalesce(@cat_id, c.cat_id)

    AND c.sub_cat_id = coalesce(@sub_cat_id, c.sub_cat_id)

    AND c.health_board_id = o.organisation_id

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

eamonroche SSCommitted Points: 1678 More actions · Answer 4

quote:
Gary
thanks for your reply
I took your advice and, with a similar version of your code:
SELECT
o.organisation_shortname as org_shortname,
sum(q.media_cost_per_pos) as tot_media_cost,
sum(q.prod_cost_per_pos) as tot_prod_cost
FROM
dbo.organisations o,
dbo.qry_positions_with_costs q
WHERE
o.is_a_health_board=1
and
q.health_board_id = o.organisation_id
However I got an error message:
column o.organisation_shortname is invalid because it is not contained in an aggregate function

gljjr SSChampion Points: 10080 More actions · Answer 5

Sorry about that! That's what happens when you don't read what you write!

You need to add a group by clause for the organisation_shortname field.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

eamonroche SSCommitted Points: 1678 More actions · Answer 6

eamonroche

SSCommitted

Points: 1678

September 5, 2003 at 2:47 am

#472758

ok.

Thanks for your help. Much appreciated.