Solved

SQL Statement: CountOfSummaryItem

Posted on 2003-12-05
10
378 Views
Last Modified: 2010-05-01
Hello All,

Is there anyway to ASC, DESC the CountOfSummaryItem in this sql?:

s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(Select count(*) FROM tbl_Records_DR)) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY DAuthor;"

Thanks guys. ADawn
0
Comment
Question by:ADawn
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 5

Expert Comment

by:rajaamirapu
ID: 9882092
s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(Select count(*) FROM tbl_Records_DR)) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY CountOfSummaryItem;"
0
 

Author Comment

by:ADawn
ID: 9882371
rajaamirapu

I've tried this. It doesn't work! I receive the error message:

"No value given for one or maore required parameters."
0
 
LVL 1

Expert Comment

by:carpe-diem
ID: 9882396
Or
s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(Select count(*) FROM tbl_Records_DR)) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY DAuthor, CountOfSummaryItem DESC;"
0
 
LVL 1

Expert Comment

by:carpe-diem
ID: 9882418
How are you using this sql statement? Is it part of a data environment for example?
0
 

Author Comment

by:ADawn
ID: 9882431
carpe-diem

Same error message:

"No value given for one or maore required parameters."

ADawn
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Expert Comment

by:rajaamirapu
ID: 9882559
Can you give the error code
0
 
LVL 9

Expert Comment

by:p_sie
ID: 9882579
s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(((Select count(*) FROM tbl_Records_DR)))) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY DAuthor, CountOfSummaryItem DESC;"

Try adding extra (( )) around the subquery, I know I always use them with subqueries, otherwise errors can occur!
Don't know if this will resolve your problem.
0
 

Accepted Solution

by:
ROTRTechTeam earned 50 total points
ID: 9884278
It is really easy, but you need to think about what you are wanting to order by.  You say you want to order by CountOfSummaryItem, but to SQL there is no such column.  That is why the other answers you've gotten won't work.  Remember that those aliases are just for the column title.  In your case you can accomplish what you want as follows:

s_Sql = "SELECT DAuthor, Count(DAuthor) As CountOfSummaryItem, _
        (Count(*)/(Select count(*) FROM tbl_Records_DR)) as Percentage _
        From tbl_Records_DR GROUP BY DAuthor Order BY DAuthor ORDER BY Count(DAuthor);"

You can add ASC or DESC after the "ORDER BY Count(DAuthor)" to order ascending or descending.


0
 

Author Comment

by:ADawn
ID: 9888083
ROTRTechTeam

Thanks for the great information. However, please note that you have two Order BY clauses which caused an error. I removed Order BY DAuthor (keeping: ORDER BY Count(DAuthor)) and the SQL works great.

This comment is for others that may want to use your GREAT works!

Thanks,

ADawn
0
 

Expert Comment

by:ROTRTechTeam
ID: 9888418
Whoops!  I copied your SQL and added my order by at the end.  Sorry about that, but thanks for the compliments!  Glad it worked for you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now