Edit the query for better performance?

Question

Hey, thanks in advance!

The query is shown below, and we have 10 products (prod1 to prod10), and need to get the count(*)  for the 10 products, is there a way to do in one query instead of 10 queries? the only difference is  AND Product ='Prod1'  for the 10 queries.

SELECT  state, town, COUNT(*) Num from TableA   WHERE myDate >= '2/5/2012' and myDate <='3/2/2012'   
   AND Product ='Prod1'
   AND colA in
   (
      SELECT colA from TableA
      WHERE myDate >= '2/5/2012' and myDate <='3/2/2012'
       GROUP BY State,town, colA

      HAVING COUNT(myID)=1
       )
         GROUP BY State,town
         ORDER BY State,town 

And sample result data:

State town  num

CA town1   1

CA town2   2

CA town3   3

DC town1   1

DC town2   2

DC town3   3

Answer 1

Add column [Product] to the "group by" clause.

SELECT  state, town, COUNT(*) Num from TableA   WHERE myDate >= '2/5/2012' and myDate <='3/2/2012'   
--   AND Product ='Prod1' 
   AND colA in
   (
      SELECT colA from Connects
      WHERE myDate >= '2/5/2012' and myDate <='3/2/2012'
       GROUP BY State,town, colA

      HAVING COUNT(myID)=1
       )
         GROUP BY State,town, Product
         ORDER BY State,town, Product; 

I can't provide further advice without knowing what is it that you are trying to accomplish.

Disclaimer: query not tested due to lack of table schema and sample data.

---

AMB

Some guidelines for posting questions...

Answer 2

I'm not sure what exactly you need, just mind reading, is this you want:

;With CTE
As
(    
SELECT  state, town, Product, COUNT(*) Num
from TableA   WHERE myDate >= '2/5/2012' and myDate <='3/2/2012'   

    AND colA in 
   (
       SELECT colA from Connects 
      WHERE myDate >= '2/5/2012' and myDate <='3/2/2012'
        GROUP BY State,town, colA 
      HAVING COUNT(myID)=1
        )
          GROUP BY State,town, Product
)          

Select	state, town,[Prod1], [Prod2], [Prod3, [Prod4], [Prod5], [Prod6], [Prod7], [Prod8], [Prod9], [Prod10]
From	CTE
Pivot	(Max(Num)
		For Product
		in ([Prod1], [Prod2], [Prod3, [Prod4], [Prod5], [Prod6], [Prod7], [Prod8], [Prod9], [Prod10])
		) myPiv
ORDER BY State,town;

Answer 3

Slight correction to Alejandro's query - we need to include Product into the list of selected fields.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog