Using the following two tables on SQL Server 2005, how would I write a query to return the First_Name, Last_Name, Order_Date and total amount of an order where any order line item amounts for any order (OD_Amount) are greater than 100. Orders Order_ID First_Name Last_Name Order_Date Order_Details Order_Detail_ID Order_ID OD_Item_N...
I have a table of products which contains some 2000 clothing products, each product has a grpIdent field and a productGroup field. when I run the following query: select count(1) from tblclothingitems ci where productGroup='hel' group by productGroup, grpIdent I get a resultset of 99 rows, according to SQL Yog, containing differen...
To take it down to brass tacks, I have a List containing 100 various records. A Car has a Year, Make and Model. I need to be able to : order this according to Year, but I need to: have all Cars that are Ford Explorers appear as a "group" at the end of my List (also ordered according to Year) I'm not sure if I'll have to...
I want to show the BookTitle, Firstname along with a COUNT of Copies. When I run the query, it gives an error saying to group. But when I group it says not a expression to group. Can someone help me? SELECT bk.BookTitle, au.FirstName, COUNT(bkc.BookCopyID), rb.ReservedDate FROM Book bk, Book_Author ba, BookCopy bkc, ReserveBook rb, Aut...
If I have used LINQ's GroupBy() method to create a grouped enumeration, is it possible to regroup that result under another key system? That is, if I grouped all of the objects by property X for one part of the code, is it possible to subsequently group that collection by property Y of property X at a later point in the code? ...
I have a table T1 with the following columns T1 ---------- ID | Name ---------- 1 Sender1 2 Receiver1 3 Receiver2 4 Sender2 Table T2 T1 --------------------- SenderID | ReceiverID --------------------- 1 2 1 3 4 2 I want to join the two tables in such a way that i want to get the sender and th...
How can I make this query work : SELECT column1.....,SUM(Hits) AS Hits FROM table WHERE SUM(Hits) > 100 GROUP BY column1..... The problem is the where clause, mysql display error : Error Code : 1111 Invalid use of group function I try to change the query to : SELECT column1.....,SUM(Hits) AS Hits FROM table WHERE...
SELECT DISTINCT rt . d_rev_id , rt . d_rev_code , rt . d_reason , rt . d_rev_status , rt . d_apb , rt . d_cb , pt . d_partid , pt . d_part_no , pt . d_ab , pt . d_abd , pt . d_status , rt . d_part_name , rt . d_part_desc , rt . d_part_type , pnv . d_pn_val , pnv . d_pn_id , cfv . d_optionname , rt . d_projectid , rt . d_abd , rt . d_apbd...
How do i identify the first row that has value for each Brand and Category, then update Column "FirstValue" as 1, else 0? e.g of expected table Date | Brands | Category | Value | FirstValue Jan 08 | A | 1 | 0 |0 Jan 08 | A | 2 | 0 |0 Jan 08 | A | 3 | 0 |0 Jan 08 | ...
Let's say we have two tables: 'Car' and 'Part', with a joining table in 'Car_Part'. Say I want to see all cars that have a part 123 in them. I could do this: SELECT Car.Col1, Car.Col2, Car.Col3 FROM Car INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id WHERE Car_Part.Part_Id = @part_to_look_for GROUP BY Car.Col1, Car.Col2, Car.Col3 ...
In my previous question I asked about storing the result of a query in a variable... now I realize the query can return multiple rows. I currently have this: SELECT @UserId = UserId FROM aspnet_Users WHERE UserName = @username And I want to do something like this: DELETE FROM some_table WHERE UserId IN ( *the ID list* ) DELETE FROM ...
I have a table of users containing the following columns: | User_ID (int) | Name (varchar) | Age (int) | Experience_Level (int) | I would like to create an sql query to output all of the IDs of people who are not unique in the combination of age and experience. My code so far: SELECT Count(*), User_ID FROM Users GROUP BY Age,E...
I am creating a report where the parameter selection = customer_name and the report is one page and has 5 tablix relating to the customer. What my team would like is to be able to select more than one customer at a time to save time; however they want a separate page per customer_name. Can this be done with multi-value parameter and if s...
You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query. I would however like access the one of the non-aggregates associated with the max. In plain english, I want a table with the oldest id of each kind. CREATE TABLE stuff ( id int, kind int, age int ); This query gives me the information I'm aft...
i have function public List<string > UserList() { var q = from i in _dataContext.PageStat group i by new { i.UserName } into ii select new { ii.Key.UserName }; } how can i return List ? ...
Hello, Given table | id | user | | 1 | 1 | | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 5 | | 2 | 6 | | 2 | 7 | | 2 | 8 | I want to write a query that will return 3 rows for id=1 and 3 rows for id=2 (order does not matter, yet I would assume that it can be enforced). So the end result should be something lik...
Does there exist a LINQ method to group a given collection into subgroups with specified number of elements I mean, something like Scala's grouped method. e.g. in Scala, List(89, 67, 34, 11, 34).grouped(2) gives List(List(89, 67), List(34, 11), List(34)). In case such a method doesn't exist, what would be the LINQ way to do it? ...
I have a linq expression that returns transactions in groups. Each transaction has a numerical value and I now need to know what is the highest value from all the transactions returned. This value is held in a field called TransactionId Here is the expression I am using to get the grouped list. var transactions = ctx.MyTran...
I have the following tables: tblPerson: PersonID | Name --------------------- 1 | John Smith 2 | Jane Doe 3 | David Hoshi tblLocation: LocationID | Timestamp | PersonID | X | Y | Z | More Columns... --------------------------------------------------------------- 40 | Jan. 1st | 3 | 0 | 0 | 0 | M...
Hi - I have a query that works on MySQL but doesn't work on Oracle, and I'm trying to convert. This is my table: unique_row_id http_session_id page_name page_hit_timestamp ---------------------------------------------------------------- 0 123456789 index.html 2010-01-20 15:00:00 1 123456789 i...