SELECT sponsor, COUNT(sponsor) * 2 AS total FROM Referrals GROUP BY sponsor
Works great, returns the sponsor and the total * 2 of their referrals because that's how much they make per referral. My problem is I need to pull more information from that table yet still maintain my grouping by sponsor. Every time I try to add another field, or even * like
SELECT *, sponsor, COUNT(sponsor) * 2 AS total FROM Referrals GROUP BY sponsor
it wants me to group on all of the fields and I only want to group on the sponsor. I'm not smart enough to know how to nest these or group part of it, etc. Hope I'm making sense.
I know that the sql 'group by' function exists but it's not working the way I want it to.
Brief background:
I'm making a report that displays, among other things,
-Project Name -Project Number -Project Manager -Fiscal year total spending
My problem is, I cannot figure out how to group all my data together based on Project Number and Manager. Each project has several records for Fiscal Year total spending but the same information for project name, number, and manager.
For example, Project 1 called Test has Manager A and 19 enteries for Fiscal Year total spending (i.e. it's the past 19 year's worth of data).
The report includes a header section that lists the project name, project number, and manager. Below the header information is a list of the Fiscal year info. Code:
I have an ASP project that I want to return grouped records from a SQL database.
The data fields are, for example: Company = TEST, Portfolio = port1 Company = TEST2, Portfolio = port2 Company = TEST, Portfolio = port3 Company and Portfolio would be the fields in a table named "data".
What I need to do is display the results grouped by company. For Example: TEST -- port1 -- port3 TEST2 -- port2
I have not been able to get anywhere with this and hope someone can help. The only thing that got me remotely close was "GROUP BY" but it only works with one field. I need to return all of the data fields.
I am stumped as to how to do what appears to be a very simple thing. Want to display the results of a query containing fields ID and Item, grouped by the ID. I only want 1 instance of the ID to appear on the page as a header for each group. There are 10 IDs in the query results. Thought I could use <% Response.Write (rsName("Item")) WHERE ID=1 %>, but doesn't work. How else could I accomplish this?
I have a list in a database that I have categorised using a category field. I now want to retrieve all records but grouped by categories.
how do i get my asp to loop through each category creating a recordset for each one rather than hard code a new recordset for each category i would rather it did it dynamically if thats possible ?
so eg
category travel 1 - B&B's records (recid 1) 2- rail info (recid 2)
category local info 1 - swimming baths (recid 3) 2 - museum info (recid 4)
I have a Table (Table-name:Problem_List) and Column (Column-name:Title). There are many records in this table with the same (or similar) titles. What i'm trying to achieve, is a list of this column, but instead of 2000 rows, I need the similar names counted list like this...
Blocker : (100) Serious : (34) Warning : (430) etc.
I am writing a report using ASP and am having trouble displaying the data in the way i want. I'm collecting turnovers for various companies and each of the companies has a county code. e.g. Yorkshire is defined as number 2.
I need to group the companies together by county and need to display a totals line for those members only and then on the following lines I want the next county and its companies.
I can group the counties together but the totals line I'm displaying appears after each companies rather than after the group of companies for a given county.
I use a do while loop to check that there is data in the database for each company. I tried putting the totals line outside the loop and that only shows 1 totals line for ALL companies.
I've got a lot of referer variables in my dbase and I'd like to have a query that groups them just by the domain. Everything up to the 3rd "/" should do it. So far, I'm using:
SELECT COUNT(id) AS clicktotal, ref FROM clicklogs GROUP BY ref
This only lists everything by the actual referring url. Is there a way to only have it group by the domain within the referring url?
I have a FAQ section on this website I'm developing that is *supposed* to pull data from three different tables: tblFAQ, tblAssignedPanels, and tblPanels.
In tblFAQ, there are 4 columns: faqID, panelID, question, and answer. The panelID links the FAQ with the associated panel in tblPanels based on the same field (panelID). tblPanels also holds the name of the panel that is associated w/ the ID. Code:
My code retrieves a username and a password from a form. Then this information is compared to some usernames and passwords that are stored in a database. The important thing here is that the comparison must be case sensitive meaning that "passWord" is not the same thing as "password"
I have this code, working fine in access 2003
SQL = "SELECT * FROM users WHERE StrComp(username_column,'" & entered_username_in_form & "',0) = 0 AND StrComp(password_column,'" & entered_password_in_form & "',0) = 0"
but get the following error when I run it against my sql 2005 database.
[Microsoft][SQL Native Client][SQL Server]'StrComp' is not a recognized built-in function name.
I don't know the corresponding t-sql for the query.
I have an Access database with two fields that I would like to group by. The two fields are location and department. Field names are "Location" and "DeptName".
I have the code correct for grouping by location. That code looks like this: Code:
I am running a querry on an access database and have set the number of records/page displayed at 20. if there are more than 20 records returned, then 1st page will show the first 20, the next page will show next 20 and so on....
The trouble:
the count of total records displayed is correct and the first page is displayed correctly. But when i click on *Next* to go to the next page, all the records of the database get displayed (not the 2nd page of records from the query).
In Access you use "*" + [passed variable] + "*", + can be replaced with & Calling a parameterized query in Access requires % be used in place of *, however, all that I have read show dynamic SQL passed to Access:
WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
However, my call is similar to:
conn.qMyLookup strVar, rs
If I modify the query in Access to:
"%" & [passed variable] & "%"
I get all records. If I only put it at the end, as suggested, I only get matches at the end, not throughout the column. Code:
I can't write the sum of a certain colum in a table of a db.
<% sql_Sum_Tax="SELECT SUM(vtax) AS sql_Sum_Tax_RS_Var FROM orderstats WHERE vcompletedate BETWEEN "& startDate &" AND "& endDate & ";" Set sql_Sum_Tax_RS = Server.CreateObject("ADODB.Recordset") sql_Sum_Tax_RS.Open sql_Sum_Tax, conn1 %>
I have an Access database of literature that I want to search using an ASP page using multiple search criteria (author, title, year, discipline,etc). I'm using a form to collect the criteria from the user and then sending it to an ASP page. I'm creating the SQL statement using variables that pick up values passed from the form.
Code:
strSQL = "SELECT * FROM Literature WHERE " & _ "(Author Like '%" & mauthor & "%') AND " & _ "(Title Like '%" & mtitle & "%') AND " & _ "(Journal Like '%" & mjournal & "%') AND "& _ "(ArticleBook Like '%" & mBookOption & "%') AND " & _ "(Year Like '%" & myear & "%') " & _ "ORDER BY Title ASC;"
This would probably work if I used "OR" in my SQL, but I want to be able to use "AND." My problem is that I'm not getting records that have null values in some of these fields.
Ive been working with queries through asp on a mdb file. The biggest problem i have is that when i query a certian column that had embedded hyperlinks in it, the query also returns the value of the hyperlink. Is there any way not to have to remove the hyperlinks in the database and not have them show up on queries? Code:
I am learning ASP from the Wrox Begining E-Commerce book, which uses Visual Basic to create a DLL and it also uses MTS as well. I have been trying to find hosting not realising the problem with registering custom dll's on server and know realise that this isn't the best way to do this. I wanted to know if there was anything i could do to change this. I have read a little bit about the Global.isa file but am not sure about this. Could i just transfer all the code into the global.isa and then use it like this. My website access a sql database and uses a mts pipeline for order processing. I don't have enough experience of ASP to re-write this code and am not sure of what to do. I have tried searching the web but am not really finding any solutions that I understand.
I am trying to have the SQL query select from a table where two conditions exist. The line:
Code:
SQL = "SELECT * FROM CompsIn WHERE Out = -1 "
Works, but I want to have it also select from compsin where UserGroup = Session("UserGroup") Until I connect the login and the CompsOut pages,adding Where UserGroup='CSU836' will work just fine.
and say 4569 belongs to joseph i want to tell what josephs rank is looking at the values his rank is 1 if 124 belongs to justin then his rank is 4 the more the points the higher the rank can someone tell me how my sql query will be
Is there a way to query for a users IP address with classic ASP/VBScript?
I'm building a local app that will feature a simple logging system. I'd like to have any user be able to update a form and when submitted, the page can look up the users IP and know that this update came from "Jeff's Workstation", for example.
There would only be 40 or so users, each of whom has a static IP that I could use to perform the lookup.
Can anyone point me the right direction, or perhaps suggest a better way to approach this problem?
