The WHERE o.date ... condition makes the outer join behave like an inner join, cutting out any rows of peoplethat don't have at least one matching row in orders.
You'll have to move the condition about o.date from the WHERE clause to the joining ON:
SELECT p.id, p.name, coalesce(sum(o.price), 0.00) AS total
FROM people p
LEFT OUTER JOIN orders o
Possible with a single SELECT:
SELECT name, count(*), to_char((count(*) * 100.0
/ sum(count(*)) OVER ()), 'FM990.00" %"') AS percent
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 ...
You can use the LEAD analytic function to obtain the next row's eventtype and eventdate alongside the current row's data:
LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
eventdate >= '2015-01-01 00:00:00.00'
AND eventdate < '...
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(...
CREATE TABLE #foo
INSERT #foo(itemid,proddte,qty) VALUES
-- 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, ...
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:
-- COUNT(d.FK_User) AS UserCount, -- if you need both counts
SUM(COUNT(d.FK_User)) OVER (PARTITION BY u.Username
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 ...
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)
insert into @Product(productId, productUnitPrice)
values(4,175.99),(5 ,100.00),( 6 , 125.00)
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 ...
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
Instead, you would have to do something like this:
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:
coalesce(sum(sc.score1),0) + coalesce(...
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
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 ...
Another way to add aliases (for derived tables and their columns) in standard SQL would be (unfortunately it hasn't been implemented in Oracle):
sum(count_employees) as total_count,
( select count(*), employID from table1...
select count(*), employID from table2...
I can't speak for the query accessing the view but the samples you provided give a quick insight into the performance of the view.
You are getting a filter operator at the end of all of your joins (INNER & LEFT OUTER) to be able to satisfy the where clause:
WHERE (matter.sid IS NULL AND data.matterId = '') OR matter.sid IS NOT NULL
Splitting the view up ...
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 ...
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 ...
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 ...
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.
SELECT t = ...
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 ...
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:
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:
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
WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:...
You could use GROUP BY GROUPING SETS and the GROUPING() function:
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)
GROUP BY GROUPING SETS ((a_id), ())
ORDER BY idx ;
In older versions (that don't have GROUPING SETS) ...
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 ...
You need to group by a substring of source:
COUNT(DISTINCT user_id) AS user_count
WHERE shop_id = 2
GROUP BY shop_id, substring(source,1,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.
WHERE (CAST(floatvalue AS numeric) - round(CAST(floatvalue AS ...