MySQL: SELECT ROUND( 100.0 * ( SUM(IF(cust_id = 541, 1, 0)) / COUNT(order_id) ), 1) AS percent_total FROM orders; Edit I guess it helps if I would have noticed the postgres tag. I thought it was a MySQL question. PostgreSQL: SELECT ROUND( 100.0 * ( SUM(CASE WHEN cust_id = 541 THEN 1 ELSE 0 END)::numeric … Read more
There are several ways that you can make this code faster (each one should be faster than the preceding ones, but it gets progressively less idiomatic-slick): Run insertOrUpdateAll instead of insertOrUpdate if on slick-pg 0.16.1+ await(run(TableQuery[FooTable].insertOrUpdateAll rows)).sum Run your DBIO events all at once, rather than waiting for each one to commit before you run … Read more
Yes, at least one case is considerably slower. If you only define the following index: ALTER TABLE … ADD INDEX (a, b); … then the query SELECT * FROM … WHERE B = 1; will not use that index. When you create an index with a composite key, the order of the columns of the … Read more
Could you do a union in the query? SELECT -1 AS ID , ‘ALL THINGS’ AS NAMEOFTHING FROM DUAL /*’FROM DUAL’ is an Oracle thing, not sure if you need to do something like that in DB2*/ UNION SELECT ID, NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING Apparently, this is how it should be done for … Read more
It is possible, but it is not advisable. You need to enclose the column name in double quotes. create table my_table (“MY COLUMN” number); But note the warning in the documentation: Note: Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be … Read more
You can call reset_queries() from the django.db module. from django.db import reset_queries reset_queries()
Try this query: SELECT uid FROM subscribes GROUP BY uid HAVING COUNT(*) > 2 AND max( CASE “subscription_type” WHEN ‘type1’ THEN 1 ELSE 0 END ) = 0
For SQL Server (and other products with windowed functions): SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY value desc) as rn FROM UnnamedTable ) t WHERE t.rn = 1
Try: select x.*, avg(dailyusage) over(partition by productid order by productid, date rows between 6 preceding and current row) as rolling_avg from (select productid, date, sum(usagecount) as dailyusage from tbl group by productid, date) x Fiddle: http://sqlfiddle.com/#!6/f674a7/4/0 Replace “avg(dailusage) over….” with sum (rather than avg) if what you really want is the sum for the past … Read more