file no frequency in sql 2008
-
i have a file_no field in my accounting_payment table and i want to run a query to see the frequency of every file, means how many times every file is listed in the table?
-
i have a file_no field in my accounting_payment table and i want to run a query to see the frequency of every file, means how many times every file is listed in the table?
use the count() and group by operators
Select count(*),file_no from accounting_payment group by file_no
Never underestimate the power of human stupidity RAH
-
use the count() and group by operators
Select count(*),file_no from accounting_payment group by file_no
Never underestimate the power of human stupidity RAH
But I also want to assign the Top number#? For example
Select TOP 5 count(*), file_no from accounting_payment
But to show the TOP (frequency) not the Top (position) Similarly I want to do it for the amount, for example:
Select TOP 5 max(amount_paid), file_no from accounting_payment
-
But I also want to assign the Top number#? For example
Select TOP 5 count(*), file_no from accounting_payment
But to show the TOP (frequency) not the Top (position) Similarly I want to do it for the amount, for example:
Select TOP 5 max(amount_paid), file_no from accounting_payment
Do some research in BOL into rownumber() and partition. Using these and a sub select you can get the top N records grouped by yourcriteria and ranked according to your sort requirements.
Never underestimate the power of human stupidity RAH
-
But I also want to assign the Top number#? For example
Select TOP 5 count(*), file_no from accounting_payment
But to show the TOP (frequency) not the Top (position) Similarly I want to do it for the amount, for example:
Select TOP 5 max(amount_paid), file_no from accounting_payment
May be you can do like this, SELECT TOP 5 FREQUENCY, FILE_NO FROM (SELECT COUNT(*) FREQUENCY, FILE_NO FROM ACCOUNTING_PAYMENT GROUP BY FILE_NO) A ORDER BY FREQUENCY DESC