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

Post New Web Links

Group by Clause in Union Query

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server

Table 1

Qty BatchNo MaterialCode

54 211 127

50 AR0347 165

252 054 190

180 022 191

50 HP10004 194

20 005010 196


Table 2

Qty BatchNo MaterialCode

6 211 127

28 054 190

20 023 191


Select Sum(iQty) as iQty ,vBatchNo,vMaterialCode




 a.iQty as Qty,      

 a.vBatchNo   as  BatchNo,      

 a.vMaterialCode &

View Complete Post

More Related Resource Links

Need Help in LINQ query for group By with chunks of record


I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&

SQL Query Where Clause



FROM dbo.SSD a , dbo.SSAAppEAL b
AND (a.AssocClaim = 1)
AND b.somedate = SELECT MAX (b1.somedate)
                   FROM dbo.SSAAppEAL b1
                   WHERE b1.SSN = b.SSN
                   AND b1.somedate < sysdate

When ever I add this Query to my Select Statement. SQL management studio is shutting down abruptly.

The mail fault is with this statement

b.somedate = SELECT MAX (b1.somedate)
                   FROM dbo.SSAAppEAL b1
                   WHERE b1.SSN = b.SSN
                   AND b1.somedate < sysdate

Can any one help me out


Content Query with more thatn on group


I want to group the result of the content query web part in two levels but this web part only have one Grouping field. How can I do grouping with more than one field?

<site> and DocumentSubtype (custom property)


  • Site 1
  •   Document Sub type 1
  •        File 1
  •        File 2
  •   Document Sub type 2
  • Site 2
  • Document Sub type 1

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Linq Query Union

HelloI need to use Union for below Linq queries. I could use Union it if it was simple "select new whith{" but Now that I Specified Class, I encounter Error using unionDim CStep() As New List(Of ProductModel) CStep= (From p In db.Produts _ Where p.CategoryID= 1 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).Union (From p In db.Produts _ Where p.CategoryID= 2 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).ToList()

Group Query

Hi All. Greetings. I have 3 TablsTable =PersonID Person ---------- ----------- 1 ali2 abuTable = DetailID Person_ID Detail---------- ----------- --------------1 1 Good2 1 Excellent 3 2 Normal Table = ReferenceID Detail_ID description---------- ----------- ------------1 1 Has son2 2 Drop3 2 DiedI want result Like this.Ali----------GoodExcellentHas sonAbu----------NormalDropDiedAbove are 3 tables. I want to Show Person Name and then Its sub link or subcategory. If i use only 2 tables then i canshow required result but i have database where i have to fetch the record from 3rd and 4th table and bring under sub category.Thanks

SQL Query keeps failing on Where clause

I am very new to SQL so forgive me for this. We are upgrading to Dynamics AX 2009 and in doing so I have been volunteered as the report writer. Lucky me. I have a query that should return all items for a customer and their resective OnHand quantity in one field and in another field it should only return an OnHand quantity if certain conditions are met in another field, in this case a location field (example; product in Embargo). Also this Embargo could be a variation of locations, (example) louembarg, embargff, louembargun, etc. I have cleaned my query back up since I believe my path was way off track. Below the query is an example of what the end result should be. SELECT     TOP (100) PERCENT dbo.INVENTTABLE.DATAAREAID, dbo.INVENTTABLE.CUSTACCOUNT, dbo.INVENTTABLE.CUSTDIVISIONID,                       dbo.INVENTTABLE.PACKAGINGGROUPID, dbo.INVENTTABLE.ITEMGROUPID, dbo.INVENTTABLE.ITEMID, dbo.INVENTTABLE.NAMEALIAS,                       dbo.INVENTTABLE.ITEMNAME, dbo.INVENTTABLE.STANDARDPALLETQUANTITY, dbo.INVENTTABLE.BOMUNITID, dbo.INVENTDIM.WMSLOCATIONID,            &n

T-SQL 2005 query for Group BY and not GROUP in same query for SQL reporting service use purpose?

Hi, I have SQL 2005 table like bellow @OrderTable I want to display all row data and GROUP BY data as well for SQL Reporting Service Matrix purpose...   declare @OrderTable TABLE (OrderID varchar(10),OrderType varchar(20),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06','O1',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06','O2',105.48,'2010-06-12') INSERT INTO @OrderTable VALUES('P07','O3',555.00,'2010-06-09') INSERT INTO @OrderTable VALUES('P08','O1',10.22,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O1',55.66,'2010-03-17') INSERT INTO @OrderTable VALUES('P06','O1',45.44,'2010-03-17') INSERT INTO @OrderTable VALUES('P07','O3',477.81,'2010-03-18') INSERT INTO @OrderTable VALUES('P07','O3',78.85,'2010-03-18') INSERT INTO @OrderTable VALUES('P06','O1',78.08,'2010-04-09') INSERT INTO @OrderTable VALUES('P07','O2',899.90,'2010-04-22') INSERT INTO @OrderTable VALUES('P08','O3',25.33,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',859.01,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',7433.89,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O1',1005.41,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O2',455.20,'2010-06-09') INSERT INTO @OrderTable VALUES('P07','O3',85.30,'2010-06-12') INSERT INTO @OrderTable VALUES

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Get the row position of the group? merging query?

Hello! I am using SQLS2005. I have two tables: Unit: UnitId int PK Title varchar UnitOption: UnitOptionId int PK UnitId int FK Title varchar Quote: QuoteId int PK UnitOptionId int FK Title varchar I want to create a scalar UDF that takes a QuoteId param and returns a varchar that contains the following description (pseudu): Quote.Title + '-' + Unit.Title + '-' + Unit.UnitId + /* Here is where my question is: If there are more than 1 UnitOption under this Unit, then return '-' + the UnitOption number under this Unit (i.e.) if under this Unit, there are 3 UnitOption with IDs 13, 17, 55 under the unit, and the current Quote.UnitOptionId is the 17 one, it should return 2. Which means I want to retrieve an ID of this row in the group. Else return '' */

Query help to write case statement in Where clause?

Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

Trying to build query using DISTINCT or GROUP BY...beginner here

Hi all, I have a table with the following format: instanceID    timeStamp  stepID 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:33.807 2 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:34.807 3 ... EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:26.773 2 so I need to build a query which will return 1 instanceID and all its stepIDs in one row. So the results would have to be something like this: instanceID    timeStamp  StepIDs 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1,2,3 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1,2 and if possible I would like to specify something like...bring me the data where 'timeStamp' > 2010-09-07 20:35 ps: I tried using DISCTINCT and GROUP BY but could not reach the desired results. Thank you!JCD

How to pass string values to parameterized sql query in Clause?

Hi,I'm using parameterized sql query to get data from database string query = "Select * from employee where employee_city in (@value)";strign city ="'NewDelhi','Bangalore','Mumbai'";I'm using following code to achive thisDataSet ds = new DataSet();SqlConnection con = new SqlConnection("Server=localhost;....");SqlCommand cmd = new SqlCommand();cmd.CommandText =query;SqlParameter param = cmd.Parameters.Add("@value",SqlDbType.VarChar);param.Value = city;SqlDataAdapter dap = new SqlDataAdapter();dap.SelectCommand = cmd;dap.Fill(ds);But this is not giving the result.If run the query in SQLServer query window as "Select * from employee where employee_city in ('NewDelhi','Bangalore','Mumbai')", records are there.But the same query will not return any records from ADO.Net.How to solve this?Thanks,Ashokan

Create SharePoint Security Group populated by AD query

Is there any non-code way to create a SharePoint Security Group that is populated by an AD query? The "standard" way of getting the same "effect" is to create a group that contains an AD group but that does not allow members of a particular site to see who else is also a member of the site Any thoughts?

XPath Query in Where clause of SQL query

Hi, I have one table where one column contains XML format data inside and i have more than one row in my table.  i.e. <test><id>1</id></test> . . . . . <test><id>N</id></test> Here  the XML id could be upto N as shown above. Now i want to query on my table and in Where clause i want to put XPath Query on this column so that i could put a condition on specific ID and based on that iD i could get the whole data row from table, i am thinking query should be as below Select * from XYZ where col1.query('//test/id') >= 5 or Select * from XYZ where col1.value('(/test/id)[1]', 'int')>= 5 Here the first query where i am using col1.query('//test/id') >= 5, There is syntax error, and in second one, first of all.. i have to declare the XML type local variable then set that vaiable like  SET @localvariable = col1 from XYZ and then @localvariable.value('(/test/id)[1]', 'int'), eventhough i cannot put the range (>=,<=) here, as well as we cannot get the all rows in @localvariable when we set the value to the local variable. but i want my query should be as i show above. How can i do this ? Any suggestion.....  --Jai JP Sharma

Group query

Hi All, Greetings.From Select statment Group query I am showing bellow recordAliHadi  | 2   | 3Jon    |  4 |  5HadiRobi | 2  | 3 Ali    |  4 |  5RobiHadi  | 2 | 3 Ali     |  4 |  5I want to show bellow result by Group query so what i wrote so it skip one category but show me in subcategory.Ali Hadi  | 2   | 3 Jon    |  4 |  5Robi Hadi  | 2 | 3 Ali     |  4 |  5Thanks

How to do a <> Select Query, and assign results to a Group 'Other'


How can I use this in a Select Query?
<> "*" & "Internet" & "*" Or <> "*" & "Old Customer" & "*" Or <> "*" & "Reference" & "*" Or <> "*" & "Saw Trucks" & "*" Or <> "*" & "Y/P" & "*" I want to group all the results (named count) and call the result 'Other'

Here’s my SQL now:

SELECT DATABASE.[LEAD FROM], Count(DATABASE.[LEAD FROM]) AS [Count of Leads], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-7,Date()) & "#") AS [Last 7-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-30,Date()) & "#") AS [Last 30-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-365,Date()) & "#") AS [Last 365-Days]



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