group-by

SQL - using alias in Group By

Just curious about SQL syntax. So if I have SELECT itemName as ItemName, substring(itemName, 1,1) as FirstLetter, Count(itemName) FROM table1 GROUP BY itemName, FirstLetter This would be incorrect because GROUP BY itemName, FirstLetter really should be GROUP BY itemName, substring(itemName, 1,1) But why can't we simply us...

MySQL include zero rows when using COUNT with GROUP BY

Hi, I am trying to perform a query which groups a set of data by an attribute called type_id. SELECT vt.id AS voucher_type, COALESCE(COUNT(v.id), 0) AS vouchers_remaining FROM vouchers v INNER JOIN voucher_types vt ON vt.id = v.type_id WHERE v.sold = 0 GROUP BY vt.id What I want in the result is the type_id and the number of unsold p...

oracle sql group count

SELECT a,b,count(*) FROM t GROUP BY rollup(a,b) result: a1, b1, 10 a1, b2, 90 a1, , 100 i need: a1, b1, 10, 100 a1, b2, 90, 100 how? ...

SQL to select events that happened at pretty much the same time

I have a database table of events that happened: (timestamp, other data...) I want to group these by things that happened at 'pretty much the same time'. That is, when ordered by timestamp, such that all events in each group are within X seconds (e.g., X=3) of some other event in that group, and more than X seconds from all events in...

Using sqlite GROUP BY in a 'conversation-like' thread

We have a VERY old Sqlite database that can NOT be resigned/changed at this late stage. It has fields like User1, User2, and ConversationDate. How would I use "GROUP BY" so that instead of 2 records output... I would only get 1? Bad output: Bob contacted Fred on 01-Jan-2007. Fred replied to Bob on 11-Jan-2007. Desired outpu...

SQL to LINQ group by

Hi, I'm trying to convert the following SQL to LINQ. I've had success with 1 group by variable but couldn't get this to work. Any help would be appreciated. select ContactID, EventID, Count=count(*) from ScanLogs s, Exhibits e where s.ExhibitID = e.ExhibitID group by s.ContactID, e.EventID The result looks something like this...

Returning subset of dictionary with a complex type as a key with Linq

I have a collection as follows Private _bankRates As Dictionary(Of RateSourceBank.Key, RateSourceBank) Where RateSourceBank.Key is simply Public Class Key Public RateType As String Public EffectiveDate As DateTime End Class How can I retrieve a subset of _bankRates that have more than one EffectiveDate per RateType? Unf...

how do i combine two selects from one table where one has a GROUP BY and the other has a DISTINCT

Both of these work individually SELECT PONumber, count(RequestNumber) as "InNumOfRequests", sum(Amount) as "SumofAmounts" FROM tableView GROUP BY PONumber SELECT DISTINCT PONumber, (10 * ceiling((DATEDIFF(day,POApprovedDate,GETDATE()))/10)) AS "BINofDaysSincePOApproved" ...

Select most recent from mysql table

I have two mysql tables: Item containing items that one can buy: CREATE TABLE `item` ( `itemid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`itemid`) ) ENGINE=InnoDB; Purchase containing all purchases: CREATE TABLE `purchase` ( `purchaseid` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAU...

Why is this defaultIfEmpty choosing ienumerable<char> instead of string?

from a in mainDoc.XPathSelectElements("//AssembliesMetrics/Assembly/@Assembly") let aVal=a.Value where aVal.IsNullOrEmpty( )==false&&aVal.Contains(" ") select aVal.Substring(0, aVal.IndexOf(' ')) into aName ...

Complex Group By Query (recursive?)

Hi, sorry for the title but I'm not sure how I should call it. I'm using PostgreSQL 8.3 and would be fine with non-ansi query proposals. Suppose this schema: TimeEntries id - int entry_date - date tracked_seconds - int project_id - int projects id - int name - string path - st...

How to simulate a group-by in JSTL view layer by keeping track of previous & current values while looping over a List?

First I retrieve the Note objects in the service layer: List<Note> notes = this.getNotes(); Then I pass List of Notes to the view layer and set the same initial value for prevNote and currNote to the first element of the List: <c:forEach items="${notes}" var="note" begin="0" end="1" step="1"> <c:set var="prevNote">${note}</c:set>...

MySQL - Searching with UNION ALL and GROUP BY

SELECT p.id, p.title, p.uri, 'post' AS search_type FROM `posts` AS p WHERE title LIKE "%logo%" UNION ALL SELECT p.id, p.title, p.uri, 'tag' AS search_type FROM posts AS p INNER JOIN post_tags AS pt ON pt.post_id = p.id INNER JOIN tags AS t ON pt.tag_id = t.id WHERE t.title LIKE "%logo%" UNION ALL SELECT p.id, p.title, p.uri, 'c...

How do I group by day when the date is stored as a long?

I need to group a query by day but the date is stored as a long, how do I do that with SQL? I need this to work across multiple vendors - oracle, db2, sql-server & mysql. The long is milliseconds, generated by java.util.Date.getTime() - which I can't change. ...

MySQL Ordering question - yet another question!

Hi again, Ok, have the following code: SELECT q21 as Comment, q21coding AS Description FROM `tresults_acme` WHERE q21 IS NOT NULL AND q21 <> '' ORDER BY q21coding = 'Other', q21coding = 'Positive comments', Count ASC This brings back the following (excerpt): Text Description La...

Grouping XML nodes by Month and Year in XSLT

Update I'd like to appologize to the people who provided answers, I seem to have caused all sorts of confusion. To avoid complicating things even further, I've removed the previous code and have added new information. Read on... I'm working on a custom Blog in Umbraco. Umbraco spits out XML as the output which is then read using XS...

SQL GROUP BY - Multiple results in one column?

Hi, I am trying to perform a SELECT query using a GROUP BY clause, however I also need to access data from multiple rows and somehow concatenate it into a single column. Here's what I have so far: SELECT COUNT(v.id) AS quantity, vt.name AS name, vt.cost AS cost, vt.postage_cost AS postage_cost FROM vouchers v INNER JOIN voucher_...

MySQL: Optimization GROUP BY multiple keys

I have a table PAYMENTS in MySql database: CREATE TABLE `PAYMENTS` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `USER_ID` BIGINT(20) NOT NULL, `CATEGORY_ID` BIGINT(20) NOT NULL, `AMOUNT` DOUBLE NULL DEFAULT NULL, PRIMARY KEY (`ID`), INDEX `PAYMENT_INDEX1` (`USER_ID`), INDEX `PAYMENT_INDEX2` (`CATEGORY_ID`), ...

Optimize this SQL query's GROUP BY on a big table.

I have this query: SELECT weekno, dt, SUM(CARRYOVER) AS CARRYOVER, daycode, COALESCE('N','N') AS FLAG, DATE_FORMAT(STR_TO_DATE(CONCAT(YEAR(dt), weekno, ' Sunday'), '%X%V %W') , '%c/%d/%Y' ) AS ENDOFTHEW...

Why does SQL Server 2008 order when using a GROUP BY and no order has been specified?

I'm running into a very strange issue that I have found no explanation for yet. With SQL Server 2008 and using the GROUP BY it is ordering my columns without any ORDER BY specified. Here is a script that demonstrates the situation. CREATE TABLE #Values ( FieldValue varchar(50) ) ;WITH FieldValues AS ( SELECT '4' FieldValue UNION AL...