group-by

case in group by in a store procedure

is possible do a case in a group by? similar to this: select * from table GROUP BY CASE WHEN @Attivita=0 THEN (RANK() OVER (GROUP BY Nome,AccountID,Matricola DESC)) END thanks ...

Getting random record from database with group by

Hello i have a question on picking random entries from a database. I have 4 tables, products, bids and autobids, and users. Products ------- id 20,21,22,23,24(prime_key) price........... etc........... users ------- id(prim_key) name user1,user2,user3 etc bids ------- product_id user_id created autobids -------- u...

sql select statement with a group by

I have data in 2 tables, and I want to create a report. Table A: tableAID (primary key) name Table B: tableBID (primary key) grade tableAID (foreign key, references Table A) There is much more to both tables, but those are the relevant columns. The query I want to run, conceptually, is this: select TableA.name, avg(TableB.gr...

LINQ to SQL, select targets with max date

I ended up with this horrible code below, I can't get a better result now. What is a better way of doing that? It's about this part of my database: EDIT A Patient has a Subscription to multiple MonitoringObjects. Target records refer to these Subscriptions. I want to retrieve the target records with the newest date per Subscription ...

How can I provide values for non-grouped columns in NHibernate?

I have a criteria query: Session.CreateCriteria<Sell043Report>() .SetProjection(.ProjectionList() .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.location)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.agent)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.cusip)) .Add(LambdaProjection...

sql: Group by x,y,z; return grouped by x,y with lowest f(z)

This is for http://cssfingerprint.com I collect timing stats about how fast the different methods I use perform on different browsers, etc., so that I can optimize the scraping speed. Separately, I have a report about what each method returns for a handful of URLs with known-correct values, so that I can tell which methods are bogus on ...

LINQ SELECT COUNT(*) AND EmployeeId

Hi, I have a table like below: EmployeeId EmployeeName RequestId RequestName EmployeeId RequestId I need to a to assign requests in a sequential fashion(those who has mininum number of requests). Can I know how to get employee who has minimum requests using linq??? Thanks, Mahesh ...

how to count NULL categories in one SQL question

hi, i have a blog application were Post belongsTo Category and Category hasMany Post Post can have a Category or not - in latter case NULL value is present in Post.category_id field. Now i would like to have following category count with single SQL query category|post_count -------------- PHP | 2 JavaScript | 4 SomeOtherCat | 1 NULL...

MySQL: filling empty fields with zeroes when using GROUP BY

I've got MySQL table CREATE TABLE cms_webstat ( ID int NOT NULL auto_increment PRIMARY KEY, TIMESTAMP_X timestamp DEFAULT CURRENT_TIMESTAMP, # ... some other fields ... ) which contains statistics about site visitors. For getting visits per hour I use SELECT hour(TIMESTAMP_X) as HOUR , count(*) AS HOUR_STAT FROM c...

Linq-to-SQL: How to shape the data with group by?

I have an example database, it contains tables for Movies, People and Credits. The Movie table contains a Title and an Id. The People table contains a Name and an Id. The Credits table relates Movies to the People that worked on those Movies, in a particular role. The table looks like this: CREATE TABLE [dbo].[Credits] ( [Id] [in...

mysql query: SELECT DISTINCT column1, GROUP BY column2

Right now I have the following query: SELECT name, COUNT(name), time, price, ip, SUM(price) FROM tablename WHERE time >= $yesterday AND time <$today GROUP BY name And what I'd like to do is add a DISTINCT by column 'ip', i.e. SELECT DISTINCT ip FROM tablename So my final output would be all the columns, from all the rows ...

SQL server query not showing daily date result

I have a simple user production report where daily quotas are tracked. The sql returns a weeks worth of data for all users and for each day it tracks their totals. The problem is if they are out for a day and have a zero production for that day then the result query just skips that day and leaves it out. I want to return days even if the...

Two separate fields need to be grouped in one group

I have two fields: Mother's employer and Father's employer, and I need to group on the employer. Could somebody help me combine the two above fields into one group? Both fields are in one table. FOr example a child named John Lewis is a record in a table and he has a father and a mother and Mother's employer is IBM and Father's emplo...

Linq-To-Objects group by

Hey, I'm building a software for timereporting I have a Dictionary<string, Dictionary<string, double>>. The key in the main dictionary is a users name and their value is a dictionary of . I have a function GetDepartment(string UserName) which returns a string with the users department. What I want is to crate a new dictionary, of t...

Better way to do SELECT with GROUP BY

Hi i've wrote a query that works: SELECT `comments`.* FROM `comments` RIGHT JOIN (SELECT MAX( id ) AS id, core_id, topic_id FROM comments GROUP BY core_id, topic_id order by id desc) comm ON comm.id = comments.id LIMIT 10 I want know if it is possible (and how) to rewrite it to get better performance. Thanks ...

How to use the distinct on databases DB4O

the data like this: file1 file2 aaaa milk aaaa red bbbb box bbbb pen cccc rose i want get result like this: file1: aaaa bbbb cccc who can tell me how to do using DB4objects waiting online.... ...

Is it possible to group rows twice in MySQL?

I have a table like this: someid somestring 1 Hello 1 World 1 Blah 2 World 2 TestA 2 TestB ... Currently I'm grouping by the id and concatenating the strings, so I end up with this: 1 Hello,World,Blah 2 World,TestA,TestB ... Is it possible to do a second grouping so...

Ordering of month/year pairs in T-SQL query

I am writing a stored procedure for displaying month and year. It is working, but it is not returning the rows in the desired order. ALTER procedure [dbo].[audioblog_getarchivedates] as begin select DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) as ArchiveDate from audio_blog a group by DateName(Month,a.creat...

Mysql Groupby and Orderby problem

Here is my data structure when i try this sql select rec_id, customer_id, dc_number, balance from payments where customer_id='IHS050018' group by dc_number order by rec_id desc; something is wrong somewhere, idk I need rec_id customer_id dc_number balance 2 IHS050018 DC3 -1 3 IHS050018 52 ...

SQL Group By Modulo of Row Count

I have the following sample data: Id Name Quantity 1 Red 1 2 Red 3 3 Blue 1 4 Red 1 5 Yellow 3 So for this example, there are a total of 5 Red, 1 Blue, and 3 Yellow. I am looking for a way to group them by Color, but with a maximum of 2 items per group (sorting is not important). ...