-1

I wrote SQL query in which I have one inner query and one outer query, My outer query produces the result on behalf of inner query, now I need to find the no of rows returning by my outer query, so what I did, I enclosed it inside another select statement and use count() function which produces the result, but i need to know more precise way to calculate the row count, please see my below query and suggest me the best way to do the same.

SELECT count(*) FROM (
SELECT 
COUNT(*) NO_OF_EMP
,SUM(tbl.AMOUNT) TOTAL_AMOUNT
,tbl.YYYYMM
,tbl.DATA_PICKED_BY_NAME
,MIN(DATA_PICKED_DATE) DATA_PICKED_DATE
,ROW_NUMBER() OVER (ORDER BY tbl.REFERENCE_ID) AS ROW_NUM
FROM (
SELECT 
SALARY_REPORT_ID
,EMP_NAME
,EMP_CODE
,PAY_CODE
,PAY_CODE_NAME
,AMOUNT
,PAY_MODE
,PAY_CODE_DESC
,YYYYMM
,REMARK
,EMP_ID
,PRAN_NUMBER
,PF_NUMBER
,PRAN_NO
,ATTOFF_EMPCODE
,DATA_PICKED_DATE
,DATA_PICKED_BY
,DATA_PICKED_BY_NAME
,SUBSTR(REFERENCE_ID,0,3) REFERENCE_ID
FROM SALARY_DETAIL_REPORT_HISTORY
WHERE PAY_CODE=999
AND REFERENCE_ID LIKE '202%'
) tbl
GROUP BY tbl.REFERENCE_ID,tbl.YYYYMM,tbl.DATA_PICKED_BY_NAME
order by tbl.YYYYMM 
)mytbl1
user9634982
  • 565
  • 5
  • 24
  • 1
    I know there is probably an answer out there for this already but I am curious too. – Sam Orozco Aug 25 '18 at 05:54
  • Here you go: https://stackoverflow.com/questions/1840538/faster-alternative-in-oracle-to-select-count-from-sometable – Sam Orozco Aug 25 '18 at 05:54
  • What do you mean by "more precise"? – sticky bit Aug 25 '18 at 06:06
  • @Sticky precise mean "specific","correct", "accurate","error-free","exact","particular","strict","rigorous" and many more, if you want more detail then google it – user9634982 Aug 25 '18 at 06:24
  • @user9634982: Ehm yes, I know the meaning of the word, thank you very much. But I don't know how the cardinality of a discrete, finite (multi) set can be more or less precise. Unless it's an estimation of some sort, it's either correct or not. And with what you have you'll get the correct and exact answer, so there is no "more precise". – sticky bit Aug 25 '18 at 06:28
  • @Sticky lol, your name is sticky but you go off topic, kindly be on the topic and suggest me the optimal way to find count of rows, thanks in advance – user9634982 Aug 25 '18 at 06:33
  • @user9634982: Optimal by what criteria? Be precise, you know the meaning of the word. ;) – sticky bit Aug 25 '18 at 06:36
  • I used three select statement, so i don't wanna use multiple select statement as it forms nested query, see my query above – user9634982 Aug 25 '18 at 06:40

1 Answers1

2

Select count distinct of the most abbreviated version of a single value of your group values from your original query:

SELECT count(distinct SUBSTR(REFERENCE_ID,0,3) || YYYYMM || DATA_PICKED_BY_NAME)
FROM SALARY_DETAIL_REPORT_HISTORY
WHERE PAY_CODE=999
AND REFERENCE_ID LIKE '202%'
Bohemian
  • 412,405
  • 93
  • 575
  • 722