none
query to first count then find sum of all values

    Question

  • SELECT players.playerkey,count(match_records.matchkey) as careermatches FROM match_records INNER JOIN players ON match_records.playerkey = players.playerkey INNER JOIN matches ON matches.matchkey = match_records.matchkey  group by match_records.matchkey.

    this query works fine, but i want one more record in same query . i want sum of all count(match_records.matchkey)

    suggest some solution.

    thanks

    jagbir

    Thursday, May 09, 2013 5:37 PM

Answers

  • One more time the same query:

    ;with cte as (SELECT players.playerkey,count(match_records.matchkey) as careermatches FROM match_records 
    INNER JOIN players ON match_records.playerkey = players.playerkey 
    INNER JOIN matches ON matches.matchkey = match_records.matchkey  group by match_records.matchkey)
    
    select *, SUM(CareerMatches) OVER () AS Total CareerMatches from cte
    
    
    

    Basically, I used your original query as CTE or derived table and then just added SUM(CareerMatches) OVER () to get the total.


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


    My blog

    Thursday, May 09, 2013 5:57 PM
  • SqlCommand cmd = new SqlCommand(@";with cte as (SELECT players.playerkey,

    count(match_records.matchkey) as careermatches FROM match_records INNER JOIN players ON match_records.playerkey = players.playerkey INNER JOIN matches ON matches.matchkey = match_records.matchkey

    group by match_records.matchkey) select *, SUM(CareerMatches) OVER () AS Total CareerMatches from cte;");



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


    My blog


    Thursday, May 09, 2013 6:06 PM

All replies

  • ;with cte as (SELECT players.playerkey,count(match_records.matchkey) as careermatches FROM match_records INNER JOIN players ON match_records.playerkey = players.playerkey INNER JOIN matches ON matches.matchkey = match_records.matchkey  group by match_records.matchkey)

    select *, SUM(CareerMatches) OVER () AS Total CareerMatches from cte


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


    My blog

    Thursday, May 09, 2013 5:47 PM
  • naomi ,query is not completely  visible.
    Thursday, May 09, 2013 5:50 PM
  • One more time the same query:

    ;with cte as (SELECT players.playerkey,count(match_records.matchkey) as careermatches FROM match_records 
    INNER JOIN players ON match_records.playerkey = players.playerkey 
    INNER JOIN matches ON matches.matchkey = match_records.matchkey  group by match_records.matchkey)
    
    select *, SUM(CareerMatches) OVER () AS Total CareerMatches from cte
    
    
    

    Basically, I used your original query as CTE or derived table and then just added SUM(CareerMatches) OVER () to get the total.


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


    My blog

    Thursday, May 09, 2013 5:57 PM
  • how to use it in c# in sqlcommand

    sqlcommand cmd=new  sqlcommand("") 

    Thursday, May 09, 2013 6:01 PM
  • SqlCommand cmd = new SqlCommand(@";with cte as (SELECT players.playerkey,

    count(match_records.matchkey) as careermatches FROM match_records INNER JOIN players ON match_records.playerkey = players.playerkey INNER JOIN matches ON matches.matchkey = match_records.matchkey

    group by match_records.matchkey) select *, SUM(CareerMatches) OVER () AS Total CareerMatches from cte;");



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


    My blog


    Thursday, May 09, 2013 6:06 PM
  • how to use in mysqlcommand  cmd=new mysqlcommand("")?
    Thursday, May 09, 2013 6:10 PM
  • Are you using SQL Server or MySQL? This forum is for Transact-SQL - the language used for SQL Server. If you're using MySQL, you need to find the appropriate forum and it would not be MSDN since it's MS technology related forums.

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


    My blog

    Thursday, May 09, 2013 6:43 PM