Strange GROUP BY Statement Output

Oct 16, 2006

I am currently straching my head as to why the following doesn't work as I expect.
I have Three tables UsersManagers, Users and Bookings:-

SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours
FROM UsersManagers
LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser
LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username
WHERE UsersManagers.UsernameManager = 'testPM1'
AND (Bookings.DateOfBooking BETWEEN '01 October 2006' AND '31 October 2006')
GROUP BY UsersManagers.UsernameUser

This statement returns only one username with the hours they have booked in my Bookings table. However the left outer joins (I would have thought) should return an entry even if there are no hours booked for a user. If I omit the Date Section:

SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours
FROM UsersManagers
LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser
LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username
WHERE UsersManagers.UsernameManager = 'testPM1'
GROUP BY UsersManagers.UsernameUser

I get two records returned
testUsr1 37.5
testUsr2 NULL

Why don't I get this when I use a date?

Thanks

View 5 Replies


ADVERTISEMENT

SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?

Jan 10, 2014

I'd like to ask how you would get the OUTPUT below from the TABLE below:

TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B

OUTPUT:

category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6

The code would go something like:

Select category, count(*), .... as id's
from TABLE
group by category

I just need to find that .... part.

View 3 Replies View Related

Strange Select Statement

Jan 26, 2007

I have a small problme with my select statement, before i explain, here are the tables i use:
forum_boardidforum_topicidboardid...
forum_answeridtopicidanswer nvarchar.....
forum_moderatorusernameboardidforum_reportusernameansweridcreated datetime
What i am trying to do is get all the reports that a moderator is allowed to see.So i tell the script what forum_moderator.username i want all the results fromThen i am trying to returnforum_report.answeridforum_report.created forum_answer.answer
I have been trying something like this:
SELECT forum_report.id, forum_report.answerid, forum_report.username, forum_answer.answer, forum_report.created FROM forum_moderators FULL OUTER JOIN forum_topics ON forum_topics.boardid = forum_moderators.boardid FULL OUTER JOIN forum_answer ON forum_answer.topicid = forum_topics.id FULL OUTER JOIN forum_report ON forum_report.answerid = forum_answer.id WHERE (forum_moderators.username = @UserName)
if forum_answer.answer = NULL then i want the program to return NULL.
When i try this i get it kinda backwards, i get:forum_report.id = nullforum_report.answerid = nullforum_report.username = nullforum_answer.answer = The actual message that i wantforum_report.created = null
What could be the problem?Patrick

View 3 Replies View Related

Strange Problem In Select Statement

Jun 30, 2004

This works:


Code:


select
a.*, b.*
FROM table1 as a
INNER JOIN table2 as b ON a.id = b.id



But this doesn't:


Code:


select
a.column1, b.column1, b.column2
FROM table1 as a
INNER JOIN table2 as b ON a.id = b.id



Returns "No. -2147217913 Data type mismatch in criteria expression". Am I just doing something stupid?

View 1 Replies View Related

Strange Behaviour In ORDER BY Statement

Jul 20, 2005

HiI've done a search and found plenty on ORDER BY problems, but mineshopefully a syntax error.This is a test segment from my stored procedure.DECLARE @Name varchar(50), @SortType IntSET @Name = "canal lock"SET @SortType = 8SELECT dbo.ite_Item.ite_IDFROM dbo.ite_Item LEFT OUTER JOINdbo.iti_ItemImages ONdbo.ite_Item.ite_ID = dbo.iti_ItemImages.iti_LinkToItemWHERE iti_ItemImages.iti_LinkToPhotoType=1AND ite_Name LIKE '%' + @Name + '%'ORDER BYCASEWHEN @SortType = 3 THEN dbo.ite_Item.ite_DateWHEN @SortType = 5 THEN dbo.ite_Item.ite_ViewsWHEN @SortType = 7 THEN dbo.ite_Item.ite_DescriptionEND ASC,CASEWHEN @SortType = 2 THEN dbo.ite_Item.ite_NameWHEN @SortType = 4 THEN dbo.ite_Item.ite_DateWHEN @SortType = 6 THEN dbo.ite_Item.ite_ViewsWHEN @SortType = 8 THEN dbo.ite_Item.ite_DescriptionEND DESC,CASEWHEN @SortType <> 2 THEN dbo.ite_Item.ite_NameEND ASCI get various errors:@SortType = 2. This is a Name field varchar(50)Syntax error converting the varchar value 'Canal Lock' to a column ofdata type smallint.I only have one smallint col and that is the ite_Views column. Why isit trying to convert a varchar(50) col?@SortType = 3. This is a ISO date field nchar(8)The conversion of the nvarchar value '20030909' overflowed an INT2column. Use a larger integer column.Why?@SortType = 7, 8. These are description fields varchar(5000)Syntax error converting the varchar value '<snip>' to a column of datatype smallint.Again, why?All other sort types are fine.What I'm thinking is that when the ORDER BY code is run, it does someconversion but what?Many thanks in advance if anyone has any ideas?Sam

View 3 Replies View Related

How To Use SP's Output In The SELECT Statement

Sep 8, 2006

hi guys!it's very very simple question for you mighty sql DBAs. but very hardfor a developer like me who is very very new to MS SQL.anyways the problem is i want to use one SPs out to in the SELectstatement. here is an example :select * from sp_tables tablename like 'syscolumns'please note that this is just an example. i'm using different SP but iwant to use in the same way.if anybody has anything to say. please write to me. i would be glade toread your repliesThanks,Lucky

View 3 Replies View Related

Output Parameter Vs Select Statement

Mar 7, 2008

If you want to return a single value should I use OUTPUT or Scaler which one is more effiecient?

View 1 Replies View Related

Creating A Heirarchical Output From SQL Statement

Jul 20, 2005

This may be a basic question, but defining anything other than a cursoris preffered.I have, as an example, 2 tables. One with customer data (addresses,phones, etc), the other is a listing of all 50 states (a cross referencefor short state alias to long state name, i.e. FL - Florida, etc...).I want to sort the out put by state long name, and show each customer inthe state ... BUT ...the output needs to be like so:FloridaABC,Inc Address1 City, State Zip, other InfoDummy Corp Address1 City, State Zip, other Info...GeorgiaXYZ, Inc Address1 City, State Zip, other Info...etc ...This is a basic heirarchical listing. Can this be done with a singleT-SQL statement or are cursors needed?Thanks in advance."Excellence is achieved through 1% inspiration and 99% perspiration." A.Einstein*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Can A Query Using OUTPUT Statement Be SELECTED From?

Oct 19, 2007

I tried wrapping a DELETE FROM ...OUTPUT DELETED.* WHERE... inside of a select, crossed my fingers and got a syntax error. I thought maybe since the OUTPUT statement produces a rowset that it and it's DELETE could be selected from. Is such a thing possible in the same query or should I give up?

View 5 Replies View Related

Select Statement To Output A Date

Oct 7, 2007

Hello Guys,

Please could you help me formulate a SELECT statement to output a date in the format (Month, Date Year), eg
October, 07 2007. Thanks.

View 5 Replies View Related

Interesting Output In A Select Statement

Nov 26, 2007

Hi,

If I query like this, am getting the following Output.. Why is the part before '_ ' gets truncated and gets displayed as result ?


SELECT 10_to_100

_to_100
-----------
10


SELECT 25_from

_from
-----------
25


If anybody is familiar with the reason,pls share...


cheers !
ash

View 5 Replies View Related

SQL Statement With Group By

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

Help With Group By Statement

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

SQL Statement Help (GROUP BY)

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

Group By Statement

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

Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage

Apr 21, 2008

An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
 
thanks

View 7 Replies View Related

Problem With SQL Statement (GROUP)

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

Using A Count If Within A Group By SQL Statement?

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

Need Help With SQL Statement - How To Group Sales?

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

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 View Related

Using Group By In Update Statement

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

Group By Case Statement

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

Group SQL Select Statement

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

Using A Variable In A GROUP BY Statement

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

How To Assign The SELECT Statement Output To A Local Variable?

May 7, 2008

 
In my program i have function that will get one value from Database.
Here i want to assign the output of the sql query to a local variable.
Its like      select emp_id    into      Num   from emp where emp_roll=222; 
here NUM  is local variable which was declared in my program.
Is it correct.?
can anyone please guide me..?

View 7 Replies View Related

Can You Have Multiple Output Parameters? Difference Between A Select Statement?

Mar 9, 2004

I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?

Do I need just a select statement to do this? What is the difference between the output and select statements?

Thanks in advance.

View 1 Replies View Related

Hiding Or Removing Column Output From Select Statement

Feb 22, 2005

I'm executing the following...

select COL1, min(COL2) from TABLE group by COL1

the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.

I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"

would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?

this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.

tia

a

View 2 Replies View Related

Formating Of Columns In Output Of A SQL Statement In Query Analyzer

Jan 16, 2004

Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
------------------------- -----------------------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---------- ----------------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

View 7 Replies View Related

SQL 2012 :: Store Procedure Only Output One Select Statement

May 28, 2014

There are about 10 select statements in a store procedure.

All select statements are need.

Is it possible to output only the result of last select statement?

View 2 Replies View Related

Transact SQL :: Unable To Get Required Output Using Case Statement

May 17, 2015

Table Structure
EID    COLA    COLB
1    name    A
1    age    23
1    city    hyd
1    email    abc@live.in
1    mobile    45126
2    name    B
2    age    43

[code]....

how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null

View 9 Replies View Related

Trouble Using Group By Statement With Query

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

Include ID Field In GROUP BY Statement

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

Get Unique Values In A Group Statement

Jul 23, 2006

Hi,
Suppose a table [Inventory]:

Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50

I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:


Table Blue,Red,Yellow 60
Chair Blue,Red 90

Does anyone has an idea how this can be achieved?

Regards,
Manolis Perrakis

View 10 Replies View Related







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