Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Statement: CountOfSummaryItem

Posted on 2003-12-05
10
Medium Priority
?
385 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

926 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