.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

SQL Query Help - Aggregate Functions Across Several Variables

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server

I have a table that looks like this….


Size                  BillCode           Qty      Weight

4                      A                     3          230

4                      B  &nbs

View Complete Post

More Related Resource Links

Built-in Functions - Aggregate Functions

Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.

SSIS Query requiring two variables

I have a query in which I need to use two variables. How do I implement this in SQL task editor for the intake of two variables????

Setting SharePoint 2010 Form Fields Using Query String Variables


Hi All,

I have a requirement to populate sharepoint form field value with query string while uploading document to document library.

While uploading the document to document library, I want to populate claim number available from query string in a pop up page i.e in my case EditForm.aspx, Located at http://servername:port/sitename/documentlibraryname/forms/EditForm.aspx.

Additional Information:

  • Created document library and added custom columns like claim number etc..
  • Created a view to filter uploaded documents as per claim number.

Server Details:

  • Windows Server 2008 R2
  • SharePoint 2010
  • SQL Server 2008 R2

I have written javascript for the same, but not sure where exactly to add the code in EditForm.aspx page.

Any help on this would be greatly appreciated.

Thanking you in advacne.



can the query be written using RANKING functions


I am of the opinion that the query below can be written using a windowing function instead of the self join.

Can anyone help with this please.


FROm #hold a 
where TransactionDate = (SELECT MAX(TransactionDate) FROM #hold b where b.PersonURN = a.PersonURN and b.DealerID = a.DealerID)

Group filters and aggregate functions


Using SSRS 2008 and SSRS 2008 R2. Found a similar post from a couple years ago but with no satisfactory answer...

It seems to make sense that aggregate functions would be applied only to data that has not been filtered out by a group filter, e.g. if only a portion of the detail or group rows are being displayed because of a filter on that group level, then displaying an aggregate value for the rows such as a count or sum should only include the data that is included on the report. And the following MSDN documentation seems to state just that:


It states that an aggregate function's scope, when it is a row or column group, "specifies the data after the group expressions and group filters are applied for the parent group and child groups. For the purposes of identifying scope containment, every parent group contains its child groups."

However, that doesn't seem to be the case. The aggregate function is applied over all of the rows within the grouping expression, including those that have been filtered out, which seems to make the group filter fairly useless, considering that one of the most common reasons for grouping in the first place is to provide aggregate totals, sums, averages, etc. for the c

Re-writing query using RANKING functions


I have the query below and I am trying to use historical queries to retrieve the information, I have put something together but running the Original query and my new query does not bring back thesame results, but i think I may be doing something incorrectly.


Why do some XML functions have such high query plan cost?


Does anyone know why this generates an expensive query plan:


SELECT @XML = '<XML Id="Hello"></XML>'

SELECT c.value('.', 'VARCHAR(MAX)') FROM @XML.nodes('//XML') T(c);

The queryplan includes two "Table Valued Function [XML Reader]"'s to occur with a cost of 61.08 each on my 2005 sp3 developer server.  If I select out an attribute then it is considerably lower:

c.value('.', 'VARCHAR(MAX)') FROM @XML.nodes('//XML/@Id') T(c);

This results in a [XML Reader] and a [XML Reader wuth XPath filter] and the cost is 1.004 for each operator.

Running tests show that they are both quick, but I am writing a stored proc for a high transaction system and always try to keep the query plan as low as possible and would not n

How to insert variables inside a query?




i've facing this problem in my program..


Dim a As Integer = 1
Do Until a = 12
            Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim cmd As System.Data.SqlClient.SqlCommand = New SqlCommand

                cmd.CommandText = "update it_report set network=(select count(*) from tbl_form_it_service where usercase in ('NETWK') and datepart(year,reportdatetime)= '2010'  and datepart(month,reportdatetime) = " & a & ") where year=2010 and month= " & month & "" 
                cmd.Connection = con


                DataGrid1.DataSource = cmd.ExecuteReader()


            Catch ex As Exception
                Message.Text = "Error! " & ex.ToString
                Exit Sub
            End Try

            a = a + 1


how can i insert  loop statement variable into the query?? and how to insert textfield id in the query also? 
2010 will be textfield value.

help me,

Using Variables in Stored Proc Query


I need to use a variable in a sql query used in a stored proc as follows:

Declare @Src varchar 100
Select * from @Src

Advice anyone?

Baruch Barness

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Introduction to Functions

A function is a section of code that is used to perform an isolated assignment. Once it has performed its assignment, the function can be accessed to present its result(s).

In Transact-SQL, a function is considered an object. After creating the function object, it becomes part of a database. You can then execute it when necessary.

Built-In Functions Fundamentals

While your primary job as a database developer consists of creating lists, probably your second most important job is to assist your users with the various assignments they must perform on your application. One way you can assist is to use functions that perform otherwise complex tasks. We introduced and described functions in the previous lesson. To assist your development with the different tasks of a database, Transact-SQL ships with various already created and tested functions. You just need to be aware of these functions, their syntax, and the results they produce.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Built-in Functions - Text and Image Functions in SQL Server

Text and image functions operate on TEXT, NTEXT and IMAGE data types. These data types are deprecated with SQL Server 2005 and should be replaced by VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) when possible. Alternatively you can also consider storing large string values using XML data type. Text and image functions are nondeterministic.

Future versions of SQL Server will not support TEXT, NTEXT and IMAGE data types. Columns with TEXT / NTEXT data type do not support commonly used string functions such as LEN, LEFT, RIGHT, etc. Furthermore, due to the large size of TEXT data you''re likely to see performance issues if you store such data in the database. However, at times you can''t help but use the TEXT / NTEXT / IMAGE data types (for example when supporting 3rd party databases when you cannot change the schema) - this is when text and image functions come in handy.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend