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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

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

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
BestAviation
Asked:
BestAviation
  • 3
1 Solution
 
YZlatCommented:
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
 
BestAviationAuthor Commented:
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
 
YZlatCommented:
loop through records in a recordset
0
 
YZlatCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now