Tag Info

Hot answers tagged

21

GROUP BY A.* is not allowed in SQL. You can bypass this by using a subquery where you group by, and then join: SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo FROM TABLE1 AS A LEFT JOIN ( SELECT FKey, COUNT(foo) AS cnt FROM TABLE2 GROUP BY FKey ) AS B ON A.PKey = B.FKey ; There is a feature in SQL-2003 standard to ...


17

In addition to @ypercube's workaround, "typing" is never an excuse for using SELECT *. I've written about this here, and even with the workaround I think your SELECT list should still include the column names - even if there are a massive number like 40. Long story short, you can avoid typing these big lists by clicking and dragging the Columns node for the ...


10

I created the table big_table according to your schema create table big_table ( updatetime datetime not null, name char(14) not null, TheData float, primary key(Name,updatetime) ) I then filled the table with 50,000 rows with this code: DECLARE @ROWNUM as bigint = 1 WHILE(1=1) BEGIN set @rownum = @ROWNUM + 1 insert into big_table ...


10

Don't know if this is the best way. I first did a select to find out if a stat is double digit and assign it a 1 if it is. Summed all those up to find out total number of double digits per game. From there just sum up all the doubles and triples. Seems to work select a.player_id, a.team, sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles, ...


8

You say: "My best educated guess is that somehow max is being used to avoid multiple grouping columns" That is correct. and then: "... but how can this return the correct results?" It returns correct results because the Symbol is the primary key in both the Investments and the Price tables. Therefore, any aggregate function over a P.column or an I.column ...


7

You are not showing the query you are using to obtain the results without diff. I'm assuming it is something like this: SELECT min = MIN(Value), max = MAX(Value), avg = AVG(Value), -- or, if Value is an int, like this, perhaps: -- AVG(CAST(Value AS decimal(10,2)) Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0) FROM atable ...


7

First things first, I notice that your 'what I do now' query: SELECT TOP (1) ca.SensorValue, ca.Date FROM sys.partitions AS p CROSS APPLY ( SELECT TOP (1) v.Date, v.SensorValue FROM SensorValues AS v WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number] AND v.DeviceId = @fDeviceId ...


7

This is a fairly generic way to do this. Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed: SELECT number FROM mytable m CROSS JOIN (SELECT 3 AS consec) x WHERE EXISTS (SELECT 1 FROM mytable WHERE number = m.number - ...


7

Try this out (worked for me on MySQL 5.5): SELECT player_id, team, SUM( ( (points >= 10) + (rebounds >= 10) + (assists >= 10) + (steals >= 10) + (blocks >= 10) ) = 2 ) double_doubles, SUM( ( (points >= 10) + (rebounds >= 10) + (assists >= 10) + (steals ...


6

I infer that your data looks like this: Person Table ╔══════════╦═══════╦════════╗ ║ PersonID ║ Name ║ Gender ║ ╠══════════╬═══════╬════════╣ ║ 1 ║ John ║ M ║ ║ 2 ║ Vicky ║ F ║ ║ 3 ║ Bob ║ M ║ ╚══════════╩═══════╩════════╝ Job Table ╔══════════╦═════════════╦════════════╗ ║ PersonID ║ JobName ║ HireDate ║ ...


6

The LEFT JOIN in @dezso's answer should be good. An index, however, will hardly be useful (per se), because the query has to read the whole table anyway - the exception being index-only scans under PostgreSQL 9.2 and favorable conditions, see below. SELECT m.hash, m.string, count(m.method) AS method_ct FROM methods m LEFT JOIN nostring n USING (hash) ...


6

This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set: WITH partitioned AS ( SELECT *, number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp FROM atable WHERE status = 'FREE' ), counted AS ( SELECT *, COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt FROM partitioned ) SELECT id_set, ...


6

You probably want something like this: SELECT h_v_charges.*, last_v.last_version FROM hist_versions_charges h_v_charges JOIN (select proj_charge_id, max(version) as last_version from hist_versions_charges where proj_sous_projet_id = 2 group by proj_charge_id ) last_v ON h_v_charges.version = ...


6

I'd first add an index on (project_id, user_id) and then in 9.3 version, try this query: SELECT u.user_id, c.number_of_nodes FROM users AS u , LATERAL ( SELECT COUNT(*) AS number_of_nodes FROM treenode AS t WHERE t.project_id = 1 AND t.user_id = u.user_id ) c -- WHERE c.number_of_nodes > 0 ; -- you probably want ...


6

Here's another take on the problem. The way I think of it, you're essentially working with pivoted data for the current problem, so the first thing to do is unpivot it. Unfortunately PostgreSQL doesn't provide nice tools to do that, so without getting into dynamic SQL generation in PL/PgSQL, we can at least do: SELECT player_id, seasonday, 'points' AS ...


5

Unless I am missing something, your query would be something like this: select created, count(*) CreatedCount from yourtable group by created order by created; See SQL Fiddle with Demo Or if you have a time associated with the date, you can use TRUNC: select trunc(created), count(*) CreatedCount from yourtable group by trunc(created) order by ...


5

The answer can be platform-specific. Although the results are the same, your performance might be very different. I would suggest another approach, because it seems to clearly document the following intent: calculate some aggregates, then decorate them with another field(s). Here is an example: WITH CustomerTotals AS( SELECT CustomerID, SUM(Amount) AS ...


5

A simple and fast variant: SELECT min(number) AS first_number, count(*) AS ct_free FROM ( SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp FROM tbl WHERE status = 'FREE' ) x GROUP BY grp HAVING count(*) >= 3 -- minimum length of sequence only goes here ORDER BY grp LIMIT 1; Requires a gapless ...


5

Use correct ANSI group by (not the MySQL abomination extension) and see what happens select sum(score) total,name,gender,dob,country from users join scores on users.id = scores.user_id where date between '2012-01-01' and '2012-01-31 23:59:59' group by name,gender,dob,country having sum(score)>=1000 order by sum(score) desc limit 50 Why? GROUP BY in ...


5

The PIVOT operator may be of help. Take a look at Books Online For example: SELECT DtStart AS Date, ISNULL([1], 0) AS [Server 1], ISNULL([2], 0) AS [Server 2], ISNULL([3], 0) AS [Server 3] FROM ( SELECT DtStart, nServerID, NDirection FROM dbo.events ) AS SourceTable PIVOT ( SUM(NDirection) ...


5

Would you believe... SELECT col1, MIN(col2), MAX(col2), MIN(col3), MAX(col3) FROM table1 GROUP BY col1; ...? each row includes the first value of col2 and col3 for each unique value of col. That assertion is not exactly true. That may be what you're seeing, but do not assume this to be meaningful and do not write code based on this ...


5

group is a reserved word (and by is another reserved word) - it's not GROUP BY that is reserved. Because it is a reserved word, it cannot be used directly as an identifier. To use a reserved word or a name with "illegal" characters (such as a space) for an identifier, you need to quote the identifier. ALTER TABLE test RENAME COLUMN sum TO "group"; Note ...


5

What @Joshua displays for MySQL, works in Postgres as well. Boolean values can be cast to integer and added up. The cast needs to be explicit, though. Makes for very short code: SELECT player_id, team , count(doubles = 2 OR NULL) AS doubledoubles , count(doubles = 3 OR NULL) AS tripledoubles FROM ( SELECT player_id, team, (points ...


4

It could be that the aggregations fit better in a subquery, and make more sense when it's that subquery that's later joined to the other table INVESTMENTS and PRICE-see below. In that case you could say that the "reason" for it could be: developer inexperience w/ SQL developer experience gained on SQL platform that doesn't support table subqueries ...


4

SELECT type, GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END ORDER BY id ASC SEPARATOR ' ') AS list_with_info, GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END ORDER BY id ASC SEPARATOR ' ') AS list_without_info FROM table1 GROUP BY type ; Tested at SQL-Fiddle: test-1 ...


4

Looks like you want something like: SELECT country , sum(case when type = 'first' then 1 else 0 end) as type_first , sum(case when type = 'second' then 1 else 0 end) as type_second , sum(case when type = 'third' then 1 else 0 end) as type_third FROM table1 GROUP BY country


4

Another way to do this if the columns are known is using an aggregate and a CASE statement: SELECT d, sum(case when loc = 'Baltimore' then v else 0 end) as Baltimore, sum(case when loc = 'Houston' then v else 0 end) as Houston, sum(case when loc = 'Chicago' then v else 0 end) as Chicago FROM test group by d order by d; See SQL Fiddle with Demo If ...


4

PostgreSQL doesn't directly support PIVOT, which is the keyword usually used on other platforms for something like this. It does have some crosstab functions in the tablefunc module. Using the crosstab() function, (an excellent StackOverflow answer)


4

If you have a limited number of values that you want to convert into columns, then this can easily be implemented using an aggregate function with a CASE expression: select user_id, sum(case when message_type = 'private' then 1 else 0 end) private, sum(case when message_type = 'public' then 1 else 0 end) public from yourtable group by user_id See SQL ...


4

Unless you are e.g. counting or summing the entire contents of a table, aggregate functions will require a group by statement. For example, if you are aggregating by counting the number of departments by employee, you must tell it to group by employee select empname, count(*) from t1 group by empname having count(*) < 37



Only top voted, non community-wiki answers of a minimum length are eligible