Tag: group-by

Not grouping by date?

I have a database table that stores document information (DocID, Name, Type, Owner, etc). Then I have another table that stores the times the document has been downloaded (DocID, DownloadTime, etc). I’m trying to set up a page for recording the number of hits that different kinds of documents (policies, forms, publications, etc) get in […]

How to relax GROUP BY restrictions in SQL Server?

Consider this query: SELECT F1,F2 FROM TABLE GROUP BY F1 Selecting F1 is valid, but F2 seems to be incorrect (after all it can change from row to row). However SQL Server does not check any logic involved here — for example F2 could be dependent of F1 (because of the JOIN clause, for example). […]

SQL Server joins, groups, and etc. to do simple number crunching

Here’s what I’m working with (SQL Server): departments: deptID, deptName students: studID, studName, deptID assignment: studID, courseID, status Students are assigned to a department and the student record holds the department’s ID number. The “assignment” is a link between a student and a course (course not shown) that holds a status (incomplete, passed, not attempted). […]

How do I search and group by a date column into sets of duration ranges?

I have a table like this; jobID – jobTitle – jobCreatedDate I want to make it possible for visitors to search in Jobs table and list it by grouping like this and I want to do it with just one SQL query: Date Added – AnyTime (20) – 1 day ago (5) – 2-7 days […]

Find the sum of profit since last transaction(same day or a previous day) at the end of a day in T-SQL

I had asked a question in April and @Mikael helped me solve it. The requirement has since changed and I am having trouble figuring it out how to build the query. The table structure is SID Sdate Profit Units 1 7/26/2010 9:15:00 AM -37.5 -1 1 7/26/2010 12:00:00 PM -125 -1 1 7/26/2010 12:45:01 PM […]

Combining multiple column values into a single column row result

I have a query that pulls in group sale orders for a venue. My current query pulls all of the info I need, but gives me multiple rows for each order number: ordCode cltClientName adrCityDesc adrStateDesc adrZipCode gsnValue2 evShow shDescr 739802 Stevens Elementary, Aberdeen WA 98520 64 183 Group – Museum Admission 739802 Stevens Elementary, […]

Ordering Ordered Groups

I have a table structure similar to the following: ID |DateUploaded|FamilyCode| 1 |01/01/2010 |FC0001 | 2 |01/01/2011 |FC0001 | 3 |01/01/2012 |FC0001 | 4 |01/08/2012 |FC0002 | 5 |01/01/2012 |FC0002 | 6 |01/08/2012 |FC0002 | 7 |01/01/2012 |FC0002 | 8 |01/08/2012 |FC0003 | 9 |01/07/2012 |FC0003 | I have a select which has the following […]

GROUP BY in SQL Server across three tables

I have found that I don’t know how to group results of query in SQL Server. I have SQL Server 2005 with three tables: domains: – id – name entity: – domain_id – user_id users: – id – login So, it’s common relationship between two tables across third table, where several domains may relate to […]

Retrieve row of aggregate on primary key with group by

Ok so I know you can’t pull specific fields without an aggregate when you perform a SQL command with a group by, but it seems to me that if you are doing an aggregate on a primary key that is guaranteed to be unique, there should be a way to pull the other rows of […]

SQL Query with counting

I am using MS-SQL and I have a table with the following columns: SessionId | Date | first name | last name i would like to do group by sessionId and then get the row with the max date. in additional to that i would like to count the sessionsId. for example: xxx | 21/12/2012 […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.