By output_field Django means to provide field type for the result of the Sum. from django.db.models import FloatField, F total_group=Sum(F(‘total_sold’)*F(‘final_price’), output_field=FloatField()) should do the trick.
Your query would work already – except that you are running into naming conflicts or just confusing the output column (the CASE expression) with source column result, which has different content. … GROUP BY model.name, attempt.type, attempt.result … You need to GROUP BY your CASE expression instead of your source column: … GROUP BY model.name, … Read more
You could avoid the ugliness of the multidimentional array and use some json which supports mixed datatypes: SELECT user_id, json_agg(json_build_array(friend_id, confirmed)) AS friends FROM friends_map WHERE user_id = 1 GROUP BY user_id Or use some key : value pairs since json allows that, so your output will be more semantic if you like: SELECT user_id, … Read more
select email, count(*) as c FROM orders GROUP BY email
With larger datasets, window functions are the most efficient way to perform these kinds of queries — the table will be scanned only once, instead of once for each date, like a self-join would do. It also looks a lot simpler. 🙂 PostgreSQL 8.4 and up have support for window functions. This is what it … Read more
for SQL Server 2017 and up use: STRING_AGG() set nocount on; declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5)) insert into @YourTable VALUES (1,1,’CCC’) insert into @YourTable VALUES (2,2,’B<&>B’) insert into @YourTable VALUES (3,2,’AAA’) insert into @YourTable VALUES (4,3,'<br>’) insert into @YourTable VALUES (5,3,’A & Z’) set nocount off SELECT t1.HeaderValue ,STUFF( (SELECT ‘, … Read more
Replace WHERE clause with HAVING, like this: SELECT o.ID , count(p.CAT) FROM Orders o INNER JOIN Products p ON o.P_ID = p.P_ID GROUP BY o.ID HAVING count(p.CAT) > 3; HAVING is similar to WHERE, that is both are used to filter the resulting records but HAVING is used to filter on aggregated data (when GROUP … Read more
You can either use GROUP BY and HAVING COUNT(*) = _: SELECT contact_id FROM your_table WHERE flag IN (‘Volunteer’, ‘Uploaded’, …) GROUP BY contact_id HAVING COUNT(*) = 2 — // must match number in the WHERE flag IN (…) list (assuming contact_id, flag is unique). Or use joins: SELECT T1.contact_id FROM your_table T1 JOIN your_table … Read more
You can just use sum(people$Weight). sum sums up a vector, and people$Weight retrieves the weight column from your data frame. Note – you can get built-in help by using ?sum, ?colSums, etc. (by the way, colSums will give you the sum for each column).
There are multiple ways of applying aggregate functions to multiple columns. GroupedData class provides a number of methods for the most common functions, including count, max, min, mean and sum, which can be used directly as follows: Python: df = sqlContext.createDataFrame( [(1.0, 0.3, 1.0), (1.0, 0.5, 0.0), (-1.0, 0.6, 0.5), (-1.0, 5.6, 0.2)], (“col1”, “col2”, … Read more