Solved

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

Posted on 2009-04-13
4
389 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

809 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