sql,postgresql,aggregate-functions,window-functions,aggregate-filter

SELECT DISTINCT ON (senderName) senderName, received -- add more columns as you please , SUM(ts_rank_cd(precompTSVector,constantTSQuery)) FILTER (WHERE precomTSVector @@ constantTSQuery) OVER (PARTITION BY senderName) AS score FROM tableA WHERE received BETWEEN timeA AND timeB ORDER BY senderName, received DESC; First you need to get DISTINCT ON right: Select first row...

postgresql,select,aggregate-functions,aggregate-filter

If each attribute only has a single value for a user, you can start by making a sparse matrix: SELECT user_id ,CASE WHEN attrib_id = 1 THEN value ELSE NULL END AS attrib_1_val ,CASE WHEN attrib_id = 2 THEN value ELSE NULL END AS attrib_2_val FROM UserAttribute; Then compress the...

sql,postgresql,count,group-by,aggregate-filter

In Postgres 9.4 there is new, cleaner aggregate FILTER option: SELECT category , count(*) FILTER (WHERE question1 = 0) AS zero , count(*) FILTER (WHERE question1 = 1) AS one , count(*) FILTER (WHERE question1 = 2) AS two FROM reviews GROUP BY 1; Details for the new FILTER clause:...

sql,postgresql,window-functions,aggregate-filter

Assuming (since it hasn't been declared) event_cd is data type integer and can be NULL. SELECT *, round(hit::numeric / at_bat, 2) AS rate FROM ( SELECT input_ts , count(*) FILTER (WHERE event_cd = ANY ('{20,21,22,23}'::int[])) OVER (ORDER BY input_ts) AS hit , count(*) FILTER (WHERE NOT (event_cd = ANY ('{11,14,15,16,17}'::int[])))...

sql,postgresql,aggregate-filter

Yes. First, you need to understand that count() simply counts the number of rows with non-NULL values, so your two counts should be the same. To get the winner, use conditional aggregation: SELECT p.id, p.name, sum(case when m.winner_id = p.id then 1 else 0 end) as matches_won, count(m.id) as matches_played...

sql,postgresql,aggregate-functions,aggregate-filter

Use a case statement to select the number if the network matches, and a 0 otherwise: SELECT sum( CASE WHEN network = 'Search Network' THEN Ctr ELSE 0 END ) ...

sql,postgresql,aggregate-functions,aggregate-filter

You would do something like: select sum(case when target_date - event_date < 30 then 1 else 0 end) as within_030, sum(case when target_date - event_date < 60 then 1 else 0 end) as within_060, sum(case when target_date - event_date < 90 then 1 else 0 end) as within_090 from event...

sql,postgresql,aggregate,date-arithmetic,aggregate-filter

Use date_trunc() to calculate timestamp bounds: SELECT id, default_code , (SELECT SUM(product_uom_qty) FROM sale_order_line c WHERE c.product_id = a.id ) AS "Total Sold" , (SELECT SUM(product_uom_qty) FROM sale_order_line c WHERE c.product_id = a.id AND c.create_date >= date_trunc('month', now()) - interval '2 month' AND c.create_date < date_trunc('month', now()) - interval '1...

sql,postgresql,aggregate-functions,aggregate-filter

Since this is a quest against "long and creepy", the query can be considerably shorter, yet. Even in pg 9.3 (or actually any version): SELECT u.name , count(g.winner_id > 0 OR NULL) AS played , count(g.winner_id = u.id OR NULL) AS won , count(g.winner_id <> u.id OR NULL) AS lost...