CODEWITHSUNDEEP
sqlsql-server-2005countquery-optimization
Saad Bhutto
Saad Bhutto

Reputation: 397

SQL server Query Count() Optimization

I am using SQL Server 2005. I am using Count() 3 times in my query

select    
    p.side,
    p.uid as uid,
    count (p.side) as cside,
    (count (p.side)/ 3) as pairs,
    ((count (p.side)/ 3) * 15 ) as commisonPrice
from  
    tempComm as p
group by 
    p.side , p.uid
order by 
    p.uid;

and it gives me correct output but the problem it how can I prevent by using this duplicity in my query?

Upvotes: 0

Views: 289

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

Couple of ways for doing it.

1. Common Table Expression - Introduced in SQL Server 2005

;WITH cte as (

    select    
          p.side,
          p.uid as uid,
          count (p.side) as cside
    from  tempComm as p
    group by p.side , p.uid

)

select * , cside / 3 as pairs , (cside / 3) * 15 as commisonPrice
from cte
order by p.uid;

2. Derived Tables

select 
    side,
    uid,
    cside,
    cside/3 as pairs,
    (cside/3)*15 as commisonPrice 
from
(
    select
    p.side ,
    p.uid as uid , 
    count (p.side) as cside
    from tempComm as p 
    group by p.side , p.uid
) t
order by uid;

Other ways are using temporary tables , views in which you will have to first insert data and perform further operation on columns but i don't think they are required for your question.

Note :

  1. Both CTE and derived table can be used only once , if you require to perform multiple operations on your data , then use temporary tables.

  2. Performance wise , CTE and derived table have similar performance , its just matter of preference which one do you want more readable and undersatandable.

Upvotes: 2

Jonysuise
Jonysuise

Reputation: 1840

select 
    side,
    uid,
    cside,
    cside/3 as pairs,
    cside/3*15 as commisonPrice 
from
(
    select
    p.side ,
    p.uid as uid , 
    count (p.side) as cside
    from tempComm as p 
    group by p.side , p.uid
) t
order by uid;

Upvotes: 2

Related Questions