Solved

Executing a SELECT (..) GROUP BY (...) statement in ASP

Posted on 2009-04-13
4
394 Views
Last Modified: 2012-08-13
I know this question is a bit daft, but humor me - I'm a bit of a novice when it comes to ASP and SQL.

I'm trying to do a SUM() and GROUP BY statement and display it using ASP. Say I have the following table

O_Id ---- OrderDate ---- OrderPrice ---- Customer
1 --------- 2008/11/12 --- 1000 ---------- Hansen
2 --------- 2008/10/23 --- 1600 ---------- Nilsen
3 --------- 2008/09/02 --- 700 ------------ Hansen
4 --------- 2008/09/03 --- 300 ------------ Hansen
5 --------- 2008/08/30 --- 2000 ---------- Jensen
6 --------- 2008/10/04 --- 100 ------------ Nilsen

And I execute the following SQL statement

"SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer"

I know that the result should look like this

Customer ---- SUM(OrderPrice)
Hansen ------- 2000
Nilsen --------- 1700
Jensen -------- 2000

But how does the ASP code that created this actually look like?

The example above is from http://www.w3schools.com/sql/sql_groupby.asp btw - A great introduction to SQL GROUP BY, but unless I have some sample code where it is actually used in ASP I'm affraid I don't know how to actually use it....

Any help would be appreciated.
0
Comment
Question by:BestAviation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 24130152
you posted your question in the wrong area. Should have tried ASP
<%
''connect the database
strConn = "Provider=SQLOLEDB;Data Source=YOURSERVERNAME;Initial Catalog=YOURDBNAME;User ID=youruser;Password=yourpwd;"
objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strConn)


SET rs = objConn.Execute("SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer")
%>
0
 

Author Comment

by:BestAviation
ID: 24130309
I thought I did that...

That is how far I get as well - to the execution of the SQL. It's the bit that comes after I'm unsure about...

What I'm asking is how do I display the html table below using ASP.

Customer ---- SUM(OrderPrice)
Hansen ------- 2000
Nilsen --------- 1700
Jensen -------- 2000
0
 
LVL 35

Expert Comment

by:YZlat
ID: 24130454
loop through records in a recordset
0
 
LVL 35

Accepted Solution

by:
YZlat earned 250 total points
ID: 24130498
you can display them in a table format:

Dim fld, i

  Response.Write "<table border=1>"
Response.Write "<tr>"
 
  Response.Write "<td>Customer</td>"
   Response.Write "<td>SUM(OrderPrice)</td>"
 
  Response.Write "</tr>"
For i=1 To rs.RecordCount

   If Not rs.EOF Then
    Response.Write "<tr>"
    For Each fld in rs.Fields
     Response.Write "<td>" & fld.Value & "</td>"
    Next
    Response.Write "</tr>"
   rs.MoveNext
   End If
Next

  Response.Write "</table>"

0

Featured Post

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!

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

751 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