Best approach for mutiple counts

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 2000 Forums

SQL Server Development (2000)

Best approach for mutiple counts

Author

Topic

thiruna
Starting Member

41 Posts

Posted - 2004-07-22 : 05:00:34

Hi,

I have the following tables....

Table tender
tender_id(PK - identity)
tender_desc (varchar)

Table bidders
bidder_id (PK - Identity)
bidder_name (varchar)

Table tender_bidders
tender_id (FK)
bidder_id (FK)
bidder_interest2bid bit (1-Yes / 0-No)

Table tender_quotations
tender_id (FK)
bidder_id (FK)
quotation_id (FK)

Quotation_id comes from quotation table which has got the details about the quotation. Here, Each vendor can submit any number of quotations

I have the following Stored procedure which list all tenders with total no. of bidders, no. of interested to bid and no. of unique bidder quotations.

SELECT ten.tender_desc,
total_bidders=
(
SELECT count(*)
FROM tender_bidders
WHERE tender_id =ten.tender_id
),
total_interested=
(
SELECT count(*)
FROM tender_bidders
WHERE tender_id =ten.tender_id
AND bidder_interest2bid =1
),
bidder_response =
(
SELECT count(DISTINCT bidder_id)
FROM tender_quotations
WHERE tender_id =ten.tendor_id
)
FROM
tenders AS ten

Now, Is there any alternate best way (T-SQL) to get these results. I would like to more about the performance. can someone please help on this????

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 05:20:24

That's probably OK.

Alternatively this sort of approach, but I've no idea if its faster (in this particular example it gets two of the values in one pass which may help)


SELECT	ten.tender_desc, 
	total_bidders = SUM(total_bidders),
	total_interested = SUM(total_interested),
	bidder_response = SUM(bidder_response)
FROM	tenders AS ten
	JOIN
	(
		SELECT	tender_id, 
			[total_bidders] = count(*),
-- Assuming interest2bid cannot be NULL
			[total_interested] = SUM(CONVERT(int, bidder_interest2bid)),
			[bidder_response] = 0
		FROM	tender_bidders
		UNION ALL
		SELECT	tender_id, 0, 0, count(DISTINCT bidder_id) 
		FROM	tender_quotations
	) X
	ON X.tender_id = ten.tender_id
GROUP BY ten.tender_desc

Kristen

thiruna
Starting Member

41 Posts

Posted - 2004-07-22 : 05:35:47

Thanks for the quick suggesstion, The bidder_interest2bid field may contain NULL values if the bidder doesnt respond. If he responds back, then it becomes 0-No or 1-Yes

Please advice...

thanks
thiru

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 05:46:03

Change:

[total_interested] = SUM(CASE WHEN bidder_interest2bid = 1 THEN 1 ELSE 0 END),

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 09:05:59

I would go with this approach:


select 
   ten.Tender_Id, ten.tender_desc, 
		ISNULL(Bids.totalBidders,0) as TotalBidders,
		ISNULL(Bids.total_interested,0) as TotalInterested, 
		ISNULL(Quotes.Bidder_Reponse,0) as BidderResponse
from
   tender ten
left outer join
   (select tender_id, count(*) as total_Bidders, 
           sum(bidder_interest2bid) as total_Interested
    from tender_bidders
    group by tender_id) Bids
on
   ten.tender_Id = Bids.Tender_id
left outer join
    (select tender_id, count(distinct bidder_id) as bidder_response
     from tender_quotations
     group by tender_id) Quotes
on
   ten.tender_Id = Quotes.Tender_id

I often feel it is cleaner to break down these SQL statements a little, to truly reflect logically what you wish to return. if the goal is to return 1 row per tender_id, then start with the master table of those values and then LEFT OUTER JOIN to as many subqueries as you need (all grouped by tender_id) with the results you wish to return. From there, replace NULL values with 0 or handle those however you wish.

I personally feel almost any time you are GROUPING in a query by a non-key column in your tables (i.e.,descriptions or names or addresses), then your query can be rearranged to be more efficient and more readable as well. just a preference.

test the performance ... let us know.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 09:18:43

I'd be interested in the performance comparison to.

I like your approach Jeff, I hate those "SELECT this, that, the_other" wrappers covering a bunch of UNIONs or somesuch, you're right that your approach is clearer.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources