Hot answers tagged

10

You can use the LEAD analytic function to obtain the next row's eventtype and eventdate alongside the current row's data: SELECT eventtype, eventdate, LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype, LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate FROM atable WHERE eventdate >= '2015-01-01 00:00:00.00' AND eventdate < '...


10

This is valid syntax, too: sum(sum(qty)) over () It's a bit confusing when one sees it at first but you only have to remember that the window functions - e.g. sum() over () - are applied after the group by so everything that can appear in the select list of a group by query can be placed inside a window aggregate. So (the qty cannot but) the sum(...


9

Possible with a single SELECT: SELECT name, count(*), to_char((count(*) * 100.0 / sum(count(*)) OVER ()), 'FM990.00" %"') AS percent FROM t GROUP BY 1 ORDER BY 1; count(*) is a separate form of the function and slightly faster than count(<expression>). Assuming all columns to be NOT NULL, else you may have to use the ...


9

CREATE TABLE #foo ( itemid int, proddte date, qty int ); INSERT #foo(itemid,proddte,qty) VALUES (1,'20140101',5),(1,'20140102',7),(2,'20150101',10); -- if it really needs to be a column with the same value -- in every row, just calculate once and assign it to a variable DECLARE @sum int = (SELECT SUM(qty) FROM #foo); SELECT itemid, proddte, ...


9

You need a "window" aggregate, i.e. an OVER clause in the aggregate. And because the query already has a GROUP BY, the aggregate needed is the SUM() over the COUNT(d.FK_User) you already have: SELECT u.Username, -- COUNT(d.FK_User) AS UserCount, -- if you need both counts SUM(COUNT(d.FK_User)) OVER (PARTITION BY u.Username ...


8

This is a common problem with floating point numbers everywhere. Floating point numbers stored in computer systems should only ever be considered approximations because there are numbers easy to represent in decimal that come out longer than the available precision (sometimes they are in fact never ending) when converted to binary. See ypercube's links and ...


8

Many (perhaps, most) other database products will not allow you to define a derived table with a calculated column that has no explicitly assigned alias. In other words, they will not let you have this SELECT ... FROM ( SELECT expression FROM ... ) Instead, you would have to do something like this: SELECT ... FROM ( ...


8

You can use grouping set also declare @JobRequiredProducts table (jobId int, productId int); declare @Product table(productId int, productUnitPrice decimal(8,2)) insert into @JobRequiredProducts(jobId,productId) values(1,4),(1,5),(1,6); insert into @Product(productId, productUnitPrice) values(4,175.99),(5 ,100.00),( 6 , 125.00) SELECT j.jobId, ...


6

I believe you have a misunderstanding of what GROUP BY does. Not surprising, I had issues myself when first learning, in large part because the MySQL manual doesn't actually explicitly say what GROUP BY does, at least not that I can find (and I searched a lot, just now; plenty of caveats and special behavior, not so much an actual definition). My (on the ...


6

Another way to add aliases (for derived tables and their columns) in standard SQL would be (unfortunately it hasn't been implemented in Oracle): select sum(count_employees) as total_count, employID from ( select count(*), employID from table1... union all select count(*), employID from table2... union all ...


5

You could use a subquery but you don't need to. Just don't sum the bonus and add it in the GROUP BY list. Notice that you have to also add the student.id, even in your original query, in case you have 2 students with same name. You probably also need coalesce() for students without any scores: SELECT st.name, coalesce(sum(sc.score1),0) + coalesce(...


5

You are basically building two tables, which then has to be joined on the date column. In your query you miss the join condition. One clear way of solving this is building the two result sets as CTEs (WITH queries): WITH pc AS (SELECT planning_day, sum(sot_contribution) AS minutes_planned FROM planning_constraints ...), p ...


4

Yes, columns in the select-list cannot reference other columns by alias. This is standard SQL. MySQL does not guarantee the order of evaluation of the select-list from left to right, so it would not be a recommended practice to use variables like you show. Another workaround is to do computations and create column aliases in a derived table, and then ...


4

Maybe you should use the type conversion function CInt()? As in, CInt(expr1) + CInt(expr2). Does this fix your problem? This is necessary if you haven't defined the column data type as a numeric type.


4

The SUM() OVER does not fail, it does exactly what it is supposed to do. When you have an aggregate like SUM() with OVER (... ORDER BY something), there is a default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means that the sum goes over all rows that belong to the same partition (same Model in this case) and have the Counts value greater or ...


4

Presumably your new column has values which are a certain datetime relative to 1-Jan-1900, or some other date. ...in which case, you could rename your column, and create a calculated column with your original column name, which is the number of second (or minutes or whatever granularity you want), so that you can SUM that instead. Example: SELECT t = ...


4

I believe you are asking for the sum of "distinct" values of owgh for each value of mid and pid - a different "granularity" of data. e.g. for mid = 3 then add 1.5 + 0.6 + 1.2 + 3.0 = 6.3; only count the 1.5 once instead of twice. However if your record ids for the 1.5 were for 2 different pid values, then you would want to count the 1.5 twice. One way you ...


4

If we assume that login and logout always comes in pairs, and that login and logot are always on the same date, it's just a matter of finding the smallest time for a logout after each login. If no logout is found it means that the employee is still working so now() (if the report is run at a later date, use 23:59:59 for the date of login) is used instead: ...


4

Please check if the performance is sufficient: SELECT empid ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW()) FROM emplog b WHERE b.empid = a.empid AND b.time > ...


4

I haven't tested this extensively but I think it can do what you need. Here I assume that ID_NO is a customer identifier, TD is the date of the order, and AMT_PUR represents the amount ordered. You want all of the rows for when a customer has ordered 10 or more of anything over a 30 day date range starting with that day. Here is a little bit of test data: ...


4

Using @dezso's solution, which is the accepted answer above, here is the amended query and result for reference. WITH pc AS (SELECT date_trunc('day', start_time - INTERVAL '18 hours')::date as planning_day, sum(sot_allowed) AS minutes_allowed FROM planning_constraints WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:...


4

You could use GROUP BY GROUPING SETS and the GROUPING() function: SELECT idx = ROW_NUMBER() OVER (ORDER BY GROUPING(a_id), a_id), a = CASE WHEN GROUPING(a_id) = 0 THEN a_id ELSE 'Total' END, number = COUNT(*), value = SUM(amount) FROM #t GROUP BY GROUPING SETS ((a_id), ()) ORDER BY idx ; In older versions (that don't have GROUPING SETS) ...


4

This request is quite old, but as the accepted answer is wrong, I thought I'd add a correct one, so future readers don't get too confused. A campain has landers and conversions. If we merely join all tables, we get for a campaign with two landers and three conversions 2 x 3 = 6 result rows. If we sum or count then, we'll get wrong results (the number of ...


4

The problem with your query is simply that you're not actually processing the records in the order you want to. Your query picks up all records with an idestado_cliente less than the current row. This would work fine if all records were entered in order strictly by date. however, as we see in your test data, they are not; the last three entries are for ...


4

You need to group by a substring of source: SELECT shop_id, substring(source,1,3), COUNT(DISTINCT user_id) AS user_count FROM tickets WHERE shop_id = 2 GROUP BY shop_id, substring(source,1,3); http://rextester.com/EFZZSG27212


3

It's easy-peasy: select a,b,c,sum(d) from yourtable group by a,b,c; Probably too simple for this site, to be honest.


3

David's answer is correct except for the code, which is nonsensical in PostgreSQL. You're looking for > 2 decimal places. The easiest way to find that is to cast to NUMERIC (arbitrary-precision decimal floating point) and subtract the rounded value from the original. SELECT * FROM mytable WHERE (CAST(floatvalue AS numeric) - round(CAST(floatvalue AS ...


3

This is pretty simple to code using standard SQL if that is what you prefer. CREATE TABLE dbo.seller (SellerID INT, SellerName NVARCHAR(100)); CREATE TABLE dbo.productsold (SellerID INT, SaleDate DATE, ProductEnum TINYINT); -- Sample sellers INSERT INTO dbo.seller VALUES (1, 'Frank'); INSERT INTO dbo.seller VALUES (2, 'Sally'); -- Sample transactions ...


3

The type DOUBLE is a floating point type represented in binary internally. Binary floating point numbers will produce "rounding errors" when converted to base 10. If you want precise numeric calculations with a decimal point, you'll have to use the NUMERIC or DECIMAL type. To help you understand why floating point types have rounding errors when converting ...


3

Original You want to sum up all time ranges, except those that fit inside other time ranges on the same day: SELECT DATE_FORMAT(DAY, '%Y-%m-%d') ,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC( SUM(TIMEDIFF(TIME_EXIT,TIME_ENTER)) )), '%H:%i') AS Total FROM Table1 AS t WHERE NOT EXISTS ( SELECT * FROM Table1 WHERE DAY = t.DAY AND ...


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