CODEWITHSUNDEEP
sqlsql-server
Namal
Namal

Reputation: 2071

Getting the row count

I have following sql query. I want to get all row count. Anybody can help me?

WITH numberedrows 
     AS (SELECT DISTINCT TOP 10 f_feeid_pk, 
                                f_code, 
                                f_description, 
                                f_fee, 
                                f_areaid_ind_fk, 
                                a_name_ind, 
                                p_name_ind, 
                                s_name_ind, 
                                b_name_ind, 
                                Row_number() 
                                  OVER ( 
                                    ORDER BY b_name_ind, s_name_ind, p_name_ind, 
                                  a_name_ind, f_code ASC) AS 
                                RowNumber 
         FROM   t_fee 
                INNER JOIN t_area 
                        ON f_areaid_ind_fk = a_areaid_pk 
                INNER JOIN t_project 
                        ON a_projectid_fk = p_projectid_pk 
                INNER JOIN t_section 
                        ON p_sectionid_fk = s_sectionid_pk 
                INNER JOIN t_branch 
                        ON s_branchid_fk = b_branchid_pk 
         WHERE  f_feeid_pk > 0) 
SELECT * 
FROM   numberedrows

Upvotes: 0

Views: 111

Answers (1)

Vikdor
Vikdor

Reputation: 24124

Replace that DISTINCT TOP 10 with DISTINCT to get all the distinct results, assuming you are interested only in count of distinct, otherwise remove the DISTINCT as well, and then change your SELECT over CTE to use COUNT(*)

WITH numberedrows 
     AS (SELECT DISTINCT f_feeid_pk, 
                                f_code, 
                                f_description, 
                                f_fee, 
                                f_areaid_ind_fk, 
                                a_name_ind, 
                                p_name_ind, 
                                s_name_ind, 
                                b_name_ind, 
                                Row_number() 
                                  OVER ( 
                                    ORDER BY b_name_ind, s_name_ind, p_name_ind, 
                                  a_name_ind, f_code ASC) AS 
                                RowNumber 
         FROM   t_fee 
                INNER JOIN t_area 
                        ON f_areaid_ind_fk = a_areaid_pk 
                INNER JOIN t_project 
                        ON a_projectid_fk = p_projectid_pk 
                INNER JOIN t_section 
                        ON p_sectionid_fk = s_sectionid_pk 
                INNER JOIN t_branch 
                        ON s_branchid_fk = b_branchid_pk 
         WHERE  f_feeid_pk > 0) 
SELECT COUNT(*) FROM numberedrows;

Upvotes: 2

Related Questions