Hi to All!
Can any one help me in generating serial no in the query result?
e.g. i write a query it gives me 500 rows.
select name,fathername from abc
name FatherName
Harry David
Sarah Nenry
.
.
.
.
i want that reslut should come like that
S-no name FatherName
1 Harry David
2 Sarah Nenry
.
.
.
.
500 Farid Masood
how can i add this sequence no in query i have no S-no column?
Regards
Thanx
*** Sent via Developersdex http://www.examnotes.net ***1. Create a temp table with an Identity column
2. Insert your values into the temp table
3. Select * From the temp table and return that resultset.
Greg Jackson
PDX, Oregon|||http://www.aspfaq.com/2427
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:uBPoaM2IGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi to All!
> Can any one help me in generating serial no in the query result?
> e.g. i write a query it gives me 500 rows.
> select name,fathername from abc
> name FatherName
> Harry David
> Sarah Nenry
> .
> .
> .
> .
> i want that reslut should come like that
> S-no name FatherName
> 1 Harry David
> 2 Sarah Nenry
> .
> .
> .
> .
> 500 Farid Masood
> how can i add this sequence no in query i have no S-no column?
> Regards
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Line numbering is an issue for the front end and has nothing to do with
the RDBMS. You can use a stinking dirty kludge with a proprietary
IDENTITY if you do not care about proper coding.
Have you ever had a software engineering course or read a book on
Software Engineering?|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...
> http://www.aspfaq.com/2427
'Be sure to read KB #186133 for Microsoft's official word'
(How to dynamically number rows in a SELECT Transact-SQL statement)
Applies to 2005...?..well do the people that write this stuff read BOL?
Perhaps they want to keep some things a secret:)
www.rac4sql.net|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138413114.379715.161790@.g47g2000cwa.googlegroups.com...
> Line numbering is an issue for the front end and has nothing to do with
> the RDBMS. You can use a stinking dirty kludge with a proprietary
> IDENTITY if you do not care about proper coding.
> Have you ever had a software engineering course or read a book on
> Software Engineering?
Would you feel better substituting ranking for line numbering?
And I was having a hard time using 'stinking dirty kludge' in
a sentence.
Did you write for 'Laugh In'? :)|||>> Would you feel better substituting ranking for line numbering? <<
That is fine; ranking as a rule while putting a number on the output of
an un-ordred cursor is absurd and unrepeatable.
But do you have a bigger problem using it in a program :)? You
should.
Two gags only. And I do not remember what they were. They paid $50
for the two.|||I've posted something in the private groups so hopefully they will update to
reflect all the new bits that they brought in for SQL Server 2005.
Eg...
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"05ponyGT" <nospam@.nospam> wrote in message
news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...
>
> 'Be sure to read KB #186133 for Microsoft's official word'
> (How to dynamically number rows in a SELECT Transact-SQL statement)
> Applies to 2005...?..well do the people that write this stuff read BOL?
> Perhaps they want to keep some things a secret:)
> www.rac4sql.net
>|||select row_number() over ( order by name ), name
from sys.objects
order by name
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ui7ET6LJGHA.3408@.TK2MSFTNGP12.phx.gbl...
> I've posted something in the private groups so hopefully they will update
> to reflect all the new bits that they brought in for SQL Server 2005.
> Eg...
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
>
Showing posts with label resultset. Show all posts
Showing posts with label resultset. Show all posts
Monday, March 19, 2012
Monday, February 13, 2012
adding column in resultset from SP
I have a sp: mysp_getstuff
it contains the following:
it contains the following:
SELECT Adress,City FROM tblUserData WHERE UserName='john'
as you can see it returns 2 columns.
I also have another SP: mysp_GetNr. This sp returns an integer.
I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'
So the resultset from mysp_getstuff should be:
Adress, City, Number (in which the number column contains the result from mysp_GetNr)
How can I do that?
Use an OUTPUT Parameter to get the value from mysp_getnr.
Declare @.num int
EXEC mysp_getnr @.params... @.num OUTPUT
Then, in your mysp_getstuff you can either return @.num as its OUTPUT param or along with the result set.
SELECT Adress,City,Number=@.num FROM tblUserData WHERE UserName='john'.
Thursday, February 9, 2012
Adding all values in one column
I posed this problem a few days ago, but havent been able to generate the results i need.
Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:
Lastname totalsales orderID
--------
doe 1403 510
doe 500 680
doe 200 701
using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a new
field?
netsportsI am not clear on what you are looking for. Can you show an example of the desired output?
|||DECLARE @.sum int
SELECT
@.sum = SUM(TotalSaleS)
FROM
Accounts
This should work.
|||
"WHERE Accounts.TDate = '" + QueriedDay + "' AND SalesRep.ID = " + repID +
Better yet, use Parameterized Queries. Besides syntax issues, it also helps you against SQL Injection Attacks.|||
|||Before I get started about redundancy and waste of bandwidth andprocessing, I am going to start by saying that I'm making the widestassumption in what you're trying to do.
We're all aware of the saying that to Assume makes an ass out of youand me, so please bear with me as I make myself look foolish.
I'm assuming SalesRep.ID is the Primary Key, SalesRep.LName hasabsolutely no bearing on the record's uniqueness, andAccounts.totalsales can actually have negative values.
I'll also rewrite the sql statement within the unnecessary loop thatcreates yet another connection to the same database.. again, I won'tget into that.
SELECT
SalesRep.ID,
SalesRep.LName,
SUM(Accounts.totalSales) AS totalsalesQueriedDay
FROM
SalesRep INNER JOIN Orders
ON SalesRep.ID = Orders.SalesRep_ID
INNER JOIN Accounts
ON Orders.ID = Accounts.Order_ID
WHERE
Accounts.TDate = @.QueryDay -- assuming again that this is a procedure
AND SalesRep.ID = @.SalesRepID -- there I go assuming again
GROUP BY
SalesRep.ID,
SalesRep.LName
HAVING
(SUM(Accounts.totalsales) >= 0)
ORDER BY
SalesRep.LName -- I think this is strange to have a salesrep id with multiple last names, but that's just me.
What I'd suggest is to execute this in Query Analyzer with thedata in place of the parameters to ensure that the data exists, and youcan validate the output. I'm not sure since I never notint.ToString(), or things like that, but you could have an exceptionsomewhere that's halting your process.
Basically, you'd want a resultset that states
EmployeeID EmployeeLastName SumOfSales
100254 WhoCares 10000.00
258642 JoMomma 200000.00
Right?
|||
Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:
Lastname totalsales orderID
--------
doe 1403 510
doe 500 680
doe 200 701
using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a new
field?
netsportsI am not clear on what you are looking for. Can you show an example of the desired output?
|||DECLARE @.sum int
SELECT
@.sum = SUM(TotalSaleS)
FROM
Accounts
This should work.
|||
Terri:
Below is my older post regarding this. I have since put the sql statement in a stored procedure, and got rid of the inline coding:
Dinakar:
How would I use your code in my stored procedure for this?
==
I am trying to get all the sales reps and their combined sales totals for a given queried date, in which i loop thru (using C# while loop) the available sales reps to get their rep IDs, then match it up with their sales results for the day. The portion of my code below is successful in retrieving all the necessary rep IDs; now I have to loop thru the rep IDs and match their sales total for the given day, whereas the each individual sale is represented with the Order_ID. When running this in my Query Analyzer, i notice that it only gives me just one sale per sales rep on the given date, and not the second or third sale if multiple sales exist for the sales rep on this date. Since this is being looped with the While control statement (and it reads the data reader until there are no more available sales rep IDs), what can I add to the While statement to make sure it grabs all the Order_Ids, and then adds them up in the aggregate SUM statement?
string sqlRep = "SELECT SalesRep.ID as repID , SalesRep.LName " +
"FROM SalesRep " +
"WHERE (Terminated IS NULL) AND (tblSalesRep.StartDate < '" + QueriedDay + "') " +
"order by SalesRep.ID asc ";
SqlCommand objCommandDR = new SqlCommand(sqlRep, objConn);
objConn.Open();
///
////-/ = SqlCommand.ExecuteReader();
SqlDataReader drRep = objCommandDR.ExecuteReader();
// main query
while (drRep.Read())
{
repID = drRep.GetInt32(drRep.GetOrdinal("repID"));
LName = drRep.GetString(drRep.GetOrdinal("LName"));
strSQL = "SELECT SalesRep.ID, SalesRep.LName, SUM(Accounts.totalsales) AS totalsalesQueriedDay " +
"FROM SalesRep INNER JOIN " +
"Orders ON SalesRep.ID = Orders.SalesRep_ID INNER JOIN " +
"Accounts ON Orders.ID = Accounts.Order_ID " +
"WHERE Accounts.TDate = '" + QueriedDay + "' AND SalesRep.ID = '" + repID + "' " +
"GROUP BY SalesRep.ID, SalesRep.LName " +
"HAVING (SUM(Accounts.totalsales) >= 0) " +
"ORDER BY SalesRep.LName";
////-/Debug.WriteLine(strSQL);
// create an instance of the command-connxt object
SqlCommand objCommand = new SqlCommand(strSQL, objConn2);
objConn2.Open();
SqlDataReader drTotalsales = objCommand.ExecuteReader();
}
"WHERE Accounts.TDate = '" + QueriedDay + "' AND SalesRep.ID = " + repID +
Better yet, use Parameterized Queries. Besides syntax issues, it also helps you against SQL Injection Attacks.|||
The Sql has been set to a stored procedure now with parameters correctly coded and pointed to the sproc. I'm just wondering if there is any help you can provide in my questions I have outlined in this thread.
thanx in advance, netsports
|||Before I get started about redundancy and waste of bandwidth andprocessing, I am going to start by saying that I'm making the widestassumption in what you're trying to do.
We're all aware of the saying that to Assume makes an ass out of youand me, so please bear with me as I make myself look foolish.
I'm assuming SalesRep.ID is the Primary Key, SalesRep.LName hasabsolutely no bearing on the record's uniqueness, andAccounts.totalsales can actually have negative values.
I'll also rewrite the sql statement within the unnecessary loop thatcreates yet another connection to the same database.. again, I won'tget into that.
SELECT
SalesRep.ID,
SalesRep.LName,
SUM(Accounts.totalSales) AS totalsalesQueriedDay
FROM
SalesRep INNER JOIN Orders
ON SalesRep.ID = Orders.SalesRep_ID
INNER JOIN Accounts
ON Orders.ID = Accounts.Order_ID
WHERE
Accounts.TDate = @.QueryDay -- assuming again that this is a procedure
AND SalesRep.ID = @.SalesRepID -- there I go assuming again
GROUP BY
SalesRep.ID,
SalesRep.LName
HAVING
(SUM(Accounts.totalsales) >= 0)
ORDER BY
SalesRep.LName -- I think this is strange to have a salesrep id with multiple last names, but that's just me.
What I'd suggest is to execute this in Query Analyzer with thedata in place of the parameters to ensure that the data exists, and youcan validate the output. I'm not sure since I never notint.ToString(), or things like that, but you could have an exceptionsomewhere that's halting your process.
Basically, you'd want a resultset that states
EmployeeID EmployeeLastName SumOfSales
100254 WhoCares 10000.00
258642 JoMomma 200000.00
Right?
|||
All SQL Server aggregate functions ignore NULL except COUNT(*) so if totalsales allow NULL add COUNT(*) to SUM. Another option is supper aggregate GROUPING with ROLLUP and CUBE operators. The text below is from the BOL (books online). Hope this helps.
(This example groups royalty and aggregate advance amounts. The GROUPING function is applied to the royalty column.
USE pubs
SELECT royalty, SUM(advance) 'total advance',
GROUPING(royalty) 'grp'
FROM titles
GROUP BY royalty WITH ROLLUP
The result set shows two null values under royalty. The first NULL represents the group of null values from this column in the table. The second NULL is in the summary row added by the ROLLUP operation. The summary row shows the total advance amounts for all royalty groups and is indicated by 1 in the grp column.)
Subscribe to:
Posts (Atom)