Grouping In SQL Query

I have the following query:

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.

View Replies


ADVERTISEMENT

Grouping Query Output

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:

View Replies View Related

ASP Grouping

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.

View Replies View Related

Do Grouping

I pull the data out from the sql db. There are quite a few records the same, but diff. ID. I would like to group them. How to do it?

For example: Here is what in the DB

01. English 101 - this is a night class

02. English 101 - this is a night class

0.3 English 101 - this is a night class

I would like it displays only - English 101 this is a night class.

View Replies View Related

Recordset Grouping

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?

View Replies View Related

ASP DataBase Grouping

I am building an intranet web page using ASP that searches an Access DB. In the Database I have a field that contains dublicate words. For Ex.

[ SUBJECTS][ORG_LEVEL_1]
-------------------------------------
[Abuse ][Womens Abuse Society ]
[Abuse ][Childrens Abuse Center ]
[Baseball ][Newmarket Baseaball ***.]

When I do a search, My current Script shows the results just as it is shown there, what I would like is for it to show like this.

[Abuse ][Womens Abuse Society ]
[Childrens Abuse Center ]
[BaseBall ][Newmarket Baseball ***. ]

View Replies View Related

Grouping Records

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)

View Replies View Related

Date Grouping Problem

i'm trying to create a report in asp... and i want to group by months... for example

April
location1 $20,333
location2 $20,333
location2 $20,333

May
location1 $30,333
location2 $20,333
location2 $70,333

June
location1 $30,333
location2 $20,333
location2 $70,333

and i am pulling the date field... and example what that looks like it...

4/20/2004 12:00:00 AM
5/20/2004 12:00:00 AM
6/20/2004 12:00:00 AM

View Replies View Related

DB-Listing With Grouping & Count

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.

Running: SQL 2000 on IIS6, using ASP

View Replies View Related

Grouping And Totals For Group

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.

View Replies View Related

Grouping On Domain In Referer Log

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?

View Replies View Related

Grouping FAQs From Database

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:

View Replies View Related

ASP - Column List With Category Grouping.

I have been playing around with two different ASP scripts,

1 - To display data from a table in columns
2 - To put the data in to categories.

1 - Columns

Code:

View Replies View Related

Paging Recordset - Grouping Pages

I was wondering how some websites produce the paged numbers when displaying search results, so that if more than say 50 pages exist they group them.

For example:

Pages: 1 2 3 4 5 6 7 8 9 10 (10-20) 20-30)

Then when clicked say '10-20' the following would show:

(0-10) 11 12 13 14 15 16 17 18 19 20 (20-30)

Get the idea??

This looks a lot easier than a list of say 300 pages.

View Replies View Related

Need To Convert This Sql Query For Access To Identical Sql Query For Sql 2005..

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.

View Replies View Related

Grouping Records Within Another Group Of Records

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:

View Replies View Related

Query A DB - Pass The Query To Next Page

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).

View Replies View Related

Query Access With Multiple Query

I am using ASP/MS ACCESS to see how I can query the same database, via 2 formfields.

{name: - search}Textfield 1: - Search by Category
AND/OR By
{name: - searchT}Textfield 2: - Location

Currently,

strSearchwords = Trim(Request.QueryString("search")); where "search" is the name of Textfield1

Which is fine, but how can I set it so that on Submit, the string from search, and searchT are somehow joined together into one string?

View Replies View Related

ASP LIKE Query Using Parameterized 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:

View Replies View Related

Sql Query

Is it possible to write sql query to fetch records from
specific record number?

lets say i have one variable and it stores values such as
1,2,3 or so...

if variable contains, i want to fetch records from 1 to
20.
if it contains 2, then records should be from 21 to 40.

for 3, records would be 41 to 60...

what could be the sql for that?
i dont wannna fetch whole table and then filtering
through code..

View Replies View Related

Sql Query

i have a table called checkrenewal
it has a field called date_renewal

data is stored like this

03/20/2007
05/17/2008
10/21/2006
09/18/2006
09/04/2006

so what i want to do is display only records that are in month of sept for 2006
so there will be 2 records

so next month is Oct 2006 so only display one record

so depending on which month and which year it is display only those records.

View Replies View Related

MORE Sql Query

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

<%=sql_Orders_Placed_RS("sql_Orders_Placed_RS_Var")%>

That last line generates this error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

View Replies View Related

Sql Query

i have a table and the fields are

gender
weight
points

the records for example are

Male 55 2.0
Male 60 3.12
Male 65 3.87
Male 70 3.99

so i need to find out the points when the user selects gender and enters the weight

people are allowed to enter weights between 55 and 70

say for example someone enters weight as 55 his points will be 2.0

if someone enters weight as 64 i want the points to be 3.12

View Replies View Related

SQL Query

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.

View Replies View Related

SQL Query For Mdb

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:

View Replies View Related

ASP And DLL's Query

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.

View Replies View Related

Sql Query

I have one table with date field in sql server2000.I want to write one sql query statement that fetch last three months data from the current month..

View Replies View Related

SQL Query

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.

View Replies View Related

Query Help

i have a table called Cars which has fields like this

Car_Id
Purchase_Date

Purchase_Date has datatype as datetime in sql server

i have 2 fields on the form where the user enters the year and month and then submits the form on the next form i get the year and month like this

year=2005
month=02


i want to fire a query to get the car_id

View Replies View Related

Sql Query

i have numbers in a column say

124
2345
1356
4569

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

View Replies View Related

Query Causes CPU To Go To 100%

The following Query causes my CPU Usage to go to 100% and the page will not load: Code:

View Replies View Related

ASP SQl Query

i have two field CustomerId and a MemberID. what i want is to get the MemberID number for the max CustomerID field take for example Code:

CustomerID MemberId

100 M00
101 M01
102 M02
103 M03

so i want M03

any suggestion how to write this query in sql?

View Replies View Related

Query For IP

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?

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved