How to optimize this query so that i get the sum

select tcname,count(distinct afinishdate) from r where PERSONTYPE LIKE '%TON%'  and afinishdate<=getdate()  GROUP BY ZONESNAME,TCNAME ORDER BY TCNAME

I have this query, the output of this is tcname followed by some values.
Now what i am doing is to put all this in cursor and then finding the sum of count(distinct afinishdate).

How can i optimize if so that i find the sum(count(distinct afinishdate))
searchsanjaysharmaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
pure speculation on my part, but are you trying to arrive at a 'grand total'? maybe a CTE would server just as well?
;WITH
   CTE AS (
        SELECT
                tcname
              , COUNT(DISTINCT afinishdate) AS count_dates
        FROM r
        WHERE PERSONTYPE LIKE '%TON%'
                AND afinishdate <= GETDATE()
        GROUP BY
                 ZONESNAME --<< is this needed?
               , TCNAME
        )
SELECT
        tcname
      , count_dates
FROM (

        SELECT
                tcname
              , count_dates
              , 0 AS sort_by
        FROM CTE
        UNION ALL
        SELECT
                'Grand Total'    AS tcname
              , SUM(count_dates) AS count_dates
              , 1                AS sort_by
        FROM CTE
) AS sq
ORDER BY
        sort_by, tcname

Open in new window

and, why group by zonesname if not outputting it?
0
searchsanjaysharmaAuthor Commented:
It is done, but in the output i just need the grandtotal
0
PortletPaulfreelancerCommented:
>> i just need the grandtotal
so can you just avoid grouping then?
        SELECT
                COUNT(DISTINCT afinishdate) AS count_dates
        FROM r
        WHERE PERSONTYPE LIKE '%TON%'
                AND afinishdate <= GETDATE()

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

searchsanjaysharmaAuthor Commented:
This will not work, as the different tcname can have same afinishdate
0
PortletPaulfreelancerCommented:
:) OK. How about this?
SELECT
        SUM(count_dates) AS count_dates
FROM (
        SELECT
                tcname
              , COUNT(DISTINCT afinishdate) AS count_dates
        FROM r
        WHERE PERSONTYPE LIKE '%TON%'
                AND afinishdate <= GETDATE()
        GROUP BY
                 TCNAME
      ) AS SQ

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
searchsanjaysharmaAuthor Commented:
tx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.