Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • Last Modified:

Get row count of current SQL query

Hi
Can you advise how to get the number of rows returned by the current SQL query?
thanks
Fergal

example code below returns 4 rows, I'd like to add a column called "row count" or similar which has a value of 4 in it:
SELECT geo,Ste,CodeName,ReturnTrimMMNumber,Warranty,SUM(NetSales) AS NetSalesTotal
FROM mytable
WHERE ReturnTrimMMNumber IN('933256')
GROUP BY  geo,Ste,CodeName,ReturnTrimMMNumber,Warranty
0
fjkilken
Asked:
fjkilken
  • 3
  • 2
  • 2
  • +1
1 Solution
 
ste5anSenior DeveloperCommented:
You'll get the row count in your front-end. But this depends on your front-end..

Otherwise:

WITH    Grouped
          AS ( SELECT   M.geo ,
                        M.Ste ,
                        M.CodeName ,
                        M.ReturnTrimMMNumber ,
                        M.Warranty ,
                        SUM(M.NetSales) AS NetSalesTotal
               FROM     mytable M
               WHERE    ReturnTrimMMNumber IN ( '933256' )
               GROUP BY M.geo ,
                        M.Ste ,
                        M.CodeName ,
                        M.ReturnTrimMMNumber ,
                        M.Warranty
             )
    SELECT  G.* ,
            C.NumRows
    FROM    Grouped G
            CROSS APPLY ( SELECT    COUNT(*)
                          FROM      Grouped
                        ) C ( NumRows ); 

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you want to get the rowcount returned by the SELECT or do you just to want to see it printed somewhere?
0
 
Pawan KumarDatabase ExpertCommented:
Pls try..

SELECT geo,Ste,CodeName,ReturnTrimMMNumber,Warranty,SUM(NetSales) AS NetSalesTotal, COUNT(*) RowCounts
FROM mytable
WHERE ReturnTrimMMNumber IN('933256')
GROUP BY  geo,Ste,CodeName,ReturnTrimMMNumber,Warranty

Open in new window

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
ste5anSenior DeveloperCommented:
@Pawan: COUNT(*) retuns the number of rows used in the aggregate (GROUP BY). It does not reflect the number of rows returned by the SELECT.
0
 
Pawan KumarDatabase ExpertCommented:
thanks ste5an. Ohh...let me just change it.

May be this..Dont have anything to test now.

SELECT geo,Ste,CodeName,ReturnTrimMMNumber,Warranty,SUM(NetSales) AS NetSalesTotal, COUNT(*) OVER() RowCounts
FROM mytable
WHERE ReturnTrimMMNumber IN('933256')
GROUP BY  geo,Ste,CodeName,ReturnTrimMMNumber,Warranty

Open in new window

1
 
fjkilkenAuthor Commented:
Thanks Pawan.
Can you also advise if the COUNT(*) OVER() can be used to get a count of rows for a particular condition?
ie; ReturnTrimMMNumber can have several values, is there a way to count the number of rows for each value using the syntax you supplied?
thanks
Fergal
0
 
Pawan KumarDatabase ExpertCommented:
Yes we can...

You can use below..Here we are taking count by col1,col2..

COUNT(*) OVER(PARTITION BY col1,col2....etc)
0
 
fjkilkenAuthor Commented:
excellent!
thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now