The reason you can’t use SUM() in the WHERE clause is the order of evaluation of clauses. FROM tells you where to read rows from. Right as rows are read from disk to memory, they are checked for the WHERE conditions. (Actually in many cases rows that fail the WHERE clause will not even be … Read more
Consider this demo to bring timestamps down to a resolution of 15 minutes and aggregate resulting dupes: WITH tbl(id, ts) AS ( VALUES (1::int, ‘2012-10-04 00:00:00’::timestamp) ,(2, ‘2012-10-04 18:23:01’) ,(3, ‘2012-10-04 18:30:00’) ,(4, ‘2012-10-04 18:52:33’) ,(5, ‘2012-10-04 18:55:01’) ,(6, ‘2012-10-04 18:59:59’) ,(7, ‘2012-10-05 11:01:01’) ) SELECT to_timestamp((extract(epoch FROM ts)::bigint / 900)*900)::timestamp AS lower_bound , to_timestamp(avg(extract(epoch … Read more
You can try the following: * Updated ** — Test Data DECLARE @YourTable TABLE(Product INT, Timestamp DATETIME, Price NUMERIC(16,4)) INSERT INTO @YourTable SELECT 5678, ‘20080101 12:00:00’, 12.34 UNION ALL SELECT 5678, ‘20080101 12:01:00’, NULL UNION ALL SELECT 5678, ‘20080101 12:02:00’, NULL UNION ALL SELECT 5678, ‘20080101 12:03:00’, 23.45 UNION ALL SELECT 5678, ‘20080101 12:04:00’, NULL … Read more
I’ll answer my own question. From within any expression, it’s possible to perform lookups in all datasets. Through this way we’ll get the data: LookupSet(SourceFieldToCompare, TargetFieldToCompare, ResultField, DataSet) Now, let’s raise the bar for the question and say the data is grouped in yet another dimension, months – like this: Category | January | February … Read more
Rather than grouping, go about it like this… select * from account a join ( select account_id, row_number() over (order by account_id, id) – rank() over (order by account_id) as row_num from user ) first on first.account_id = a.id and first.row_num = 0
One way would be to use a nested query: SELECT count(*) FROM ( SELECT COUNT(Genre) AS count FROM movies GROUP BY ID HAVING (count = 4) ) AS x The inner query gets all the movies that have exactly 4 genres, then outer query counts how many rows the inner query returned.
You have to use subqueries: SELECT x.user, AVG(x.cnt) FROM (SELECT user, COUNT(answer) AS cnt FROM surveyValues WHERE study=’a1′ GROUP BY user) x GROUP BY x.user You can’t wrap an aggregate with another aggregate. You could wrap an analytic in an aggregate, if MySQL supported analytic/ranking/windowing functions…
You can very well express aggregate functions with only basic operators. It’s a pretty neat thing. Suppose we have a table T, and we’d like to find the maximum of its “value” field. First, we should take the cartesian product of T with itself — or rather with a copy of itself, T2. Then we … Read more
SELECT MIN(MinuteBar) AS MinuteBar5, Opening, MAX(High) AS High, MIN(Low) AS Low, Closing, Interval FROM ( SELECT FIRST_VALUE([Open]) OVER (PARTITION BY DATEDIFF(MINUTE, ‘2015-01-01 00:00:00’, MinuteBar) / 5 ORDER BY MinuteBar) AS Opening, FIRST_VALUE([Close]) OVER (PARTITION BY DATEDIFF(MINUTE, ‘2015-01-01 00:00:00’, MinuteBar) / 5 ORDER BY MinuteBar DESC) AS Closing, DATEDIFF(MINUTE, ‘2015-01-01 00:00:00’, MinuteBar) / 5 AS Interval, … Read more
this will give you result that has the minimum price on all records. SELECT * FROM pieces WHERE price = ( SELECT MIN(price) FROM pieces ) SQLFiddle Demo