How To Use The Sum Function With No Group By Statement
Oct 26, 2012
I want to run a much larget SQL statement, but for examples sake this is a good starting point
Code:
Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?
View 13 Replies
ADVERTISEMENT
Feb 12, 2015
I have a code like this:
SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
View 1 Replies
View Related
Nov 6, 2008
I need to group some student data by student id with each student's max test score. Here is the data:
Student A1 10/31/08 100
Student A1 11/06/08 99
Student Z2 10/31/08 75
Student Z2 11/06/08 88
I want to know for each student the max test score and what date that was achieved.
View 3 Replies
View Related
Mar 27, 2008
how do i use group by for the below query, am getting duplicate results for the query i execute, am not sure how do i use group by for this. pls.help.
SELECT
C.USER_ID,
A.comm_Name,
B.UU_TITLE,
FILENAME_URL=(Case When UU_Type = 'U' Then UU_url else UU_Filename End),
C.User_FirstName + ' ' + C.User_LastName AS Author,
Convert(CHAR(10),UU_TimeStamp,101) as DateCreated,
Convert(CHAR(10),UU_LastModified,101) as DateModified,
uu_AssocAuID,
DOWNLOADS = (select count(*) from Vportaldevstats.[dbo].stats_download A where A.stat_AuID = b.UU_AssocAuID )
FROM vportaldev.dbo.community A
INNER JOIN vportaldev.[dbo].user_upload B
ON A.comm_CreatorUserID = B.UU_USERID
INNER JOIN vportaldev.[dbo].[user] C
ON C.user_id = B.UU_USERID
thaks in advance
View 1 Replies
View Related
Apr 24, 2008
Hi All,
I would like to know if that the SUM() function can use more than 1 group in the scope portion. Example is as below:
Sum(Fields.Hour.Value, ("grp1", "grp2"))
This would help as I am doing a report using a matrix report. I need to do a calculation in the measure area so that it will calculate base on the row and column grouping. Is this possible?
Thanks & Regards,
Fadzli
View 3 Replies
View Related
Mar 19, 2007
can anyone tell me what is the use of group by in rs tables and how to use group by in tables (when we have to use )...if possible please gimme some examples..
View 1 Replies
View Related
Aug 7, 2007
Hello,
I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yet
View 6 Replies
View Related
Jun 19, 2014
select empid,orderdate from [Sales].[Orders] WHERE custid = 71
group by empid,orderdate
sample data resultset:
empid orderdate
1 2006-12-25
1 2007-12-24
1 2007-12-23
1 2008-12-23
1 2008-04-24
select empid,year(orderdate) from [Sales].[Orders] WHERE custid = 71
group by empid,year(orderdate)
sample data resultset:
empid orderdate
1 2006
1 2007
1 2008
why is the year() gets only one year when worked with groupby. when empid =1 the year 2007 has 2 records and 2008 has 2 records in first select and when I used year(orderdate) in secong select only one year for each is selected.
View 3 Replies
View Related
Dec 23, 2014
I have a query that I need to group by by using the aggregate function using MSSQL...
I want to be able to group the following columns into ONE line:
AddedDescription, OurRef, MaterialName, StockFamily, DateCreated, WJCStatusID AND THEN have WeightToSend Summed for all lines that are grouped above.
View 2 Replies
View Related
May 21, 2008
I am creating a query in MS Access. My table looks like this:
Wrk, Sample, Y
0805393, 01, 5/16/08
0805393, 02, 5/16/08
0805393, 03, 5/16/08
0805393, 03, 5/17/08
0805393, 03, 5/17/08
How can I group my data to show up like the following? I need to get the wr, sample and the most current date in MS Access.
0805393, 01, 5/16/08
0805393, 02, 5/16/08
0805393, 03, 5/17/08
I tried Max(Y), and also grouping them and it's driving me crazy!... Thanks
View 5 Replies
View Related
Jan 22, 2004
I'm trying to update a varchar field using SUM. I keep getting the error that the sub query returns more than one value.
UPDATE CIRSUB_M
SET TRM_DMO = SUBSTRING(TRM_DMO,1,11) +
(SELECT CAST(SUM(COPIES) AS VARCHAR(5)) FROM CIRSUB_M
WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')
GROUP BY PUB_CDE, DNR_NBR)
WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')
Example
PUB_CDE DNR_NBR COPIES TRM_DMO
THN 000000092637 100 A
THN 000000092637 200 B
THN 000000082455 100 A
THN 000000082455 200 B
THN 000000051779 100 A
Updated
THN 000000092637 100 A300
THN 000000092637 200 B300
THN 000000082455 100 A300
THN 000000082455 200 B300
THN 000000051779 100 A100
View 4 Replies
View Related
Apr 24, 2008
select bicycle_shop.bicycle_shop_id, bicycle_shop.company_name, order_.unit_price
from order_ inner join bicycle_shop on order_.order_serial_number = bicycle_shop.order_serial_number
order by order_.unit_price DESC;
this query will bring up a few records for each shop, of the one or more bikes they bought and each bike's price, how do i have these come out to a sum(of the price), so that each bike shop will have a sum of revenue. someone told me i need to use group by and the sum function but i dont know how
View 5 Replies
View Related
May 16, 2008
Am I correct in assuming, in partitioning scenarion - the NUMBER OF PARAMETERS in a partitioning function is equal to number of file groups?
------------------------
I think, therefore I am - Rene Descartes
View 8 Replies
View Related
Oct 24, 2006
Hi all,
I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.
/*INSERT INTO WERKS (
WERKS.Z8601,
WERKS.Z8602,
WERKS.Z8603,
WERKS.Z8604,
WERKS.Z8605,
WERKS.Z8606,
WERKS.Z8607,
WERKS.Z8608,
WERKS.Z8609,
WERKS.Z8610,
WERKS.Z8611,
WERKS.Z8621,
WERKS.Z8622,
WERKS.Z8623,
WERKS.Z8624,
WERKS.Z8625,
WERKS.Z8626,
WERKS.Z8627,
WERKS.Z8628,
WERKS.Z8629,
WERKS.Z8630,
WERKS.Z8631,
WERKS.Z8632) */
SELECT
0,
Stati.z4414,
Stati.z4402,
'',
'',
'',
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''),
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''),
'',
'',
'',
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)
FROM STATI
WHERE
(Stati.z4402 Between '40000' AND 'ZONE6') AND
(Stati.z4414 Between '2005028' AND '2005028') AND
(Stati.z4417 = 'A')
GROUP BY Stati.z4414, Stati.z4402
I get the following error:
Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.
The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS.
I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?
This query is dynamicly generated from within my program from what the user selected.
Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.
Thanks in advance!
View 5 Replies
View Related
Aug 7, 2015
Well adding it to a group by or function skews the result set. How to write this query so it displays as I need it to? This is what I have thus far, and it works as it should UNTIL I add in the line of
cast(cte.[C] As float)/cast(sum(cte.[C]) over() as float)*100 As [Rate1],
Presents the error of:
Msg 8120, Level 16, State 1, Line 35
Column 'cte.[C]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is my full on query -- with 3 CTE's involved to get me the actual result set I am after.
;with cte as
(
select
[state],
case when exists (select 1 from table2 R where R.centername = d.centername) then 1 else 0 end as [L],
case when exists (select 1 from table3 C where C.centername = d.centername) then 1 else 0 end as [C]
FROM maintable d
),
[Code] .....
View 4 Replies
View Related
Oct 17, 2007
hi everyone,
I have a little problem concerning a SQL statement I need to do.
here is my statement:
SELECT Location, [Week of Availability], GSDC_Name, PA_Name, CA_Name, MAX([Year of Availability]) AS [Year]
FROM dbo.tbl_availability
GROUP BY Location, GSDC_Name, PA_Name, CA_Name, [Week of Availability]
now the problem in this statement is that I don't want to group by Week of Availability, but I do want to select it. BUT, if I remove Week of availability from the Group by part, I get an error! how can I do this?
View 6 Replies
View Related
Mar 23, 2006
A client wants to keep track of the number of searches for keywords ina date range. So, I'm storing each occurance of a search in a table.The columns are:PK: idsearch_stringsearch_dateI'm trying to wrap my head around how I would select the number ofoccurances for each string, divided by days. The desired result wouldlook something like:search_date search_string numOccurances----------------------------------------------------------March 20 dogs 4March 20 pigs 2March 21 dogs 8March 22 pigs 3March 22 pigeons 5I've tried a query like:selectsearch_string, CONVERT(CHAR(11),search_date,106) as search_date,count(search_string) as numOccurancesfrom searcheswhere search_date >= dateadd(d,-3,getdate())group by search_date, search_stringbut it doesn't give me the desired results. I'm sure I'm just lookingat it the wrong way.Suggestions?Thanks!
View 2 Replies
View Related
Jul 20, 2005
I need this to work:SELECT [ID] FROM [test] WITH (NOLOCK) where [a/c/d]='a' GROUP BY [unit#],[EFF DATE] HAVING COUNT ([unit #]) > 1The problem is that I get an error that [ID] needs to be in the GROUPBY clause or aggregate function. if I put it in there, I will get noduplicates (because it is the identity field). The whole point ofthis is to find dups.Thanks for any help.Robby
View 3 Replies
View Related
May 19, 2008
I am working on some sql for a report and i am getting an error with the group by statement.
SELECT Member.memberNo, Member.FamilyName, Member.GivenName, Member.gender, Membership.FeesPaid, (Membership.Fees - Membership.FeesPaid) AS OutstandingFees FROM Membership
INNER JOIN Member ON Member.memberNo = Membership.memberNo
GROUP BY Member.registrationNumber;
This fails because of the GROUP BY.
Does anyone know why this is the case?
This is the error that is produced:
SELECT DISTINCT Member.memberNo, Member.FamilyName, Member.GivenName, Member.gender, Membership.FeesPaid FROM Membership
*
ERROR at line 1:
not a GROUP BY expression
If someone could help this would be greatly appreciated.
cheers,
View 3 Replies
View Related
Nov 29, 2011
best solution for this stored procedure query.I'm getting the following error:
Column 'dbo.Applicants.submitted' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Here is my select query:
Code:
SELECT DISTINCT DATENAME(MONTH, submitted) AS mon, MAX(submitted) AS SubmitDate
FROM dbo.Applicants
WHERE ((CASE WHEN MONTH(submitted) > 8 THEN YEAR(submitted) + 1 ELSE YEAR(submitted) END) = @AcYr)
ORDER BY SubmitDate
The submitted field is a date field.I don't want to add the submitted field to Group By as I want to group by month not date.Is there any solution to avoid grouping by date?
View 3 Replies
View Related
Mar 10, 2014
Consider the following dataset:
COL1 | COL2 | COL3 | COL4
1 | FD | DR. A | Y
2 | FD | DR. A | Y
3 | FD | DR. A | N
4 | FD | DR. A | Y
5 | FD | DR. A | Y
6 | PF | DR. A | Y
7 | FD | DR. B | Y
8 | PF | DR. B | N
Consider the script below:
SELECT
COL2, COL3, COUNT(COL1) AS TOTALS
FROM CASES
GROUP BY COL2, COL3
ORDER BY COL3, COL2
The script above produces the following output:
COL2 | COL3 | TOTALS
FD | DR. A | 5
PF | DR. A | 1
FD | DR. B | 1
PF | DR. B | 1
I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:
COL2 | COL3 | TOTALS | NEWCOL
FD | DR. A | 5 | 4
PF | DR. A | 1 | 1
FD | DR. B | 1 | 1
PF | DR. B | 1 | 0
I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.
View 2 Replies
View Related
Apr 13, 2015
While running the below query, getting the error: Am I missing any of the columns to include in the SELECT column_list?
Msg 8120, Level 16, State 1, Line 1
Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
<code>
select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
from
(
SELECT database_id,[Database Name]= DB_NAME(database_id),
[Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END ,
[code]...
View 3 Replies
View Related
Jan 24, 2008
Hello everyone,
I'm trying to practice some SQL statements from my Web Data Administrator. Until now everything has worked fine. However, when I write the following statement:
SELECT DATEPART(m, Date) + '/' + DATEPART(yyyy, Date) AS Month, COUNT(*) AS NumberOfEvents FROM CompanyEvents GROUP BY DATEPART(m, Date) + '/' + DATEPART(yyyy, Date)
I get the following error " Server: Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '/' to data type int."
Do I have to change something on the database?
Now, I tried to google the msg 245 and I cannot get any clear answers on the topic.
I would greatly appreciate if someone could give me some guidance on this problem.
Thanks,
Eduardo
View 2 Replies
View Related
May 21, 2008
I have the following SQL Statement:
SELECT CONVERT(char(10), FixtureDate, 101) AS Date, COUNT(*) AS 'NumberOfRecords'FROM tblFixturesGROUP BY CONVERT(char(10), FixtureDate, 101)
I want to add a new column called "need results".
This column needs to be count if a certain cell is NULL.
Count If HomeScore IS NULL
as well as grouping by date and counting the number of records. So the third column needs to count the number of records where homescore IS NULL
View 1 Replies
View Related
Apr 12, 2004
Hi,
I currently have a table whose DDL is as follows:
CREATE TABLE [tblSales] (
[OrderID] [int]
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[QtySold] [float] ,
[UnitPrice] [float] ,
[Discount] [float] ,
[GrossSaleAmount] NULL ,
[NetSaleAmount] [float]
)
The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...
QUESTION:
What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available
date is grouped/summed together.
CREATE TABLE [tbl_Product_Grouped_Sales] (
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[TotalQtySold] [float] ,
[NetSaleAmt] [float]
)
Thanks in advance for your help.
View 1 Replies
View Related
Apr 8, 2014
I recently had an interview and the interviewer has asked me to use a group by clause in an update statement. Here is the table and data:
Table name: Customer
cust Size Geo Segment
BP LG EU
Accenture LG US
MSC MED US
SclumbergerLG US
So the request is to update the Segment column with a unique number starting with number 1.
So for BP it should be 1, Accenture it should be 2, MSC it should be 3, Sclumberger should be 1 since the size and geo are not unique. He asked me to use a group by size and geo to generate a unique number for each combination and than make the update.
View 2 Replies
View Related
May 13, 2007
Hi guys,
I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below.
I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements).
Any feedback would be appreciated
SELECT distinct
PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
case when temp_111.[name] like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as GST_AMOUNT,
case when temp_111.[name] not like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as Total_Amount
FROM temp_111 INNER JOIN PERIOD
ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ
WHERE
(NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL)
AND temp_111.PERIODSEQ in ('111')
group by PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
temp_111.[name]
Here is the current result displayed:
Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000NelNULL 83470.5608000000
2006-11-01 00:00:00.000NelNULL 161408.5264874810
2006-11-01 00:00:00.000NelNULL 677568.2683000000
2006-11-01 00:00:00.000NelNULL 2645478.1215092400
2006-11-01 00:00:00.000Nel8347.0560800000 NULL
2006-11-01 00:00:00.000Nel16140.8526488160NULL
2006-11-01 00:00:00.000Nel67756.8268300000NULL
2006-11-01 00:00:00.000Nel264547.8121507070NULL
Instead I want the result to show something like this:
Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000Nel8347.0560800000 83470.5608000000
2006-11-01 00:00:00.000Nel16140.8526488160 161408.5264874810
2006-11-01 00:00:00.000Nel67756.8268300000 677568.2683000000
2006-11-01 00:00:00.000Nel264547.8121507070 2645478.1215092400
View 2 Replies
View Related
Mar 23, 2007
Hi,I am bloody amateure and I was wondering if someone could help me editthe statement below so it groups the field SCDMASTER.SCTY_CLASS_CODEand creates one field called "total balance" equallingCUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNTSELECTCUSTODY_BALANCE.APPLICATIONCYCLEDATE,CUSTODY_BALANCE.ASSET_ID,SCDMASTER.SCTY_CLASS_CODE,CUSTODY_BALANCE.OPENING_BALANCE,CUSTODY_BALANCE.DEPOSIT_AMOUNT,CUSTODY_BALANCE.WITHDRAWAL_AMOUNTFROMFPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,CAPSREPORT.SCDMASTER SCDMASTERWHERECUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND(SCDMASTER.SCTY_CLASS_CODE Not In('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))Thanks,Andreas
View 3 Replies
View Related
Sep 2, 2007
Hi Experts, I would like to make a stored procedure in my db: I have sql express 2005.
I get Error 164 when creating this procedure:
CREATE PROCEDURE CrossTable
@Variable1 smallint,
@Variable2 smallint,
@Value smallint
AS
BEGIN
SELECT @Variable1, COUNT(@Variable1) AS 'Haufigkeiten'
FROM SurveyData
WHERE @Variable2 = @Value
GROUP BY @Variable1
END
GO
I would like to generate a frequency chart per userdefined-variable (@variable) with a where restriction.
The GROUP BY @Variable1 seems to be problem: is there some workaround in order to use variables in a GROUP BY clause? or how can I write an sql statement which do the same as this procedure (CrossTable) without using the GROUP BY clause?
Thanks a lot for your replies
Greets from Switzerland
Chris
View 3 Replies
View Related
Jun 8, 2012
For code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?
The following works fine:
SELECT DISTINCT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, sn
FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''
WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a
WHERE sn IS NOT NULL
The following gives me the error:
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
CREATE FUNCTION [dbo].queryADGroupMembers
(
@group nvarchar(255)
)
RETURNS @rtnTable TABLE
[Code] .....
View 7 Replies
View Related
Feb 19, 2014
I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?
Here is my code so far:
selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,
[Code] ....
View 8 Replies
View Related
Mar 20, 2007
Hi,
I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view.
Problem: I can't associate a field with a column that i am returning with my record set.
Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname) that is associate with that loan agent and that loan state. Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header.
if the query works, my Gridview should look like this ('Name' refers to the borrower name)
UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met
Bob | | | | Name | | | |
Bob | | Name | | | | | |
Bob | | | | | | | Name |
Steve | | | Name | | | | |
Steve | | | | | | Name | |
Here is my sql call:
SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
View 2 Replies
View Related
May 15, 2007
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
And run the query:
select max(Cost),FruitName From Fruitsgroup by FruitName
It'll correctly return:
FruitName CostApple 0.63Pear 0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID FruitName Cost2 Apple 0.634 Pear 0.89
Thanks.
View 9 Replies
View Related
