Tagged Questions
2
votes
2answers
41 views
SELECT records that have top n counts for one column
I am using postgresql 9.2.
I have a dataset like this:
ID A B
1 x x
2 x x
2 x x
2 x x
3 x x
4 x x
4 x x
I want to display records with ID that has the top n ...
5
votes
2answers
68 views
Why is PostgreSQL array access so much faster in C than in PL/pgSQL?
I have a table schema which includes an int array column, and a custom aggregate function which sums the array contents. In other words, given the following:
CREATE TABLE foo (stuff INT[]);
INSERT ...
2
votes
1answer
40 views
Join across multiple tables with partial counts
Each company, has products and each product has entries in detail1, detail2, detail3 table.
**Table Company**
cid | cname
-----+-----------
100 | Company 1
101 | Company 2
**Table Product**
pid ...
0
votes
0answers
25 views
Using Django ORM to aggregate over a generated series
I have this query, which I'm trying to recreate with the Django ORM.
select
count(tickets.id),
date
from
tickets,
generate_series('2000-01-01', date_trunc('month', CURRENT_DATE), ...
1
vote
1answer
47 views
PostgreSQL aggregate function over range
I am trying to create a function that will find the intersection of tsrange, but I can't get it work:
CREATE AGGREGATE intersection ( tsrange ) (
SFUNC = *,
STYPE = tsrange
)
2
votes
1answer
28 views
Subselect on array_agg in postgresql
Is there a way to use a value from an aggregate function in a having clause in Postgresql 9.2+?
For example, I would like to get each monkey_id with a 2nd highest number > 123, as well as the second ...
2
votes
1answer
26 views
Return column based on underlying rows in aggregate query
Consider this table:
comment_id | vote | user_id
------------+------+---------
79507 | 1 | 351501
79507 | 2 | 533594
79544 | 1 | 648703
79544 | 1 | 533594
...
1
vote
3answers
46 views
how can I include a NULL value using array_agg in postgresql?
If I query this :
SELECT DISTINCT class_low
FROM groups NATURAL JOIN species
WHERE type ~~ 'faune'
AND class_high ~~ 'Arachnides'
AND (class_middle ~~ 'Araignées' OR class_middle IS NULL)
AND ...
1
vote
1answer
30 views
SqlAlchemy: count of distinct over multiple columns
I can't do:
>>> session.query(
func.count(distinct(Hit.ip_address, Hit.user_agent)).first()
TypeError: distinct() takes exactly 1 argument (2 given)
I can do:
session.query(
...
0
votes
1answer
23 views
Get distinct combinations along with count
So say in my database I have something like this:
col1 col2 col3
value1 value2 value1
value1 value1 value2
value1 value2 value1
value1 value3 value2
value2 ...
0
votes
1answer
52 views
Select sum with condition postgres sql + rails
I would like to sum the score column of the table 'recordings' group by user id and later I would like to compare the sum of score of each user. for this I wrote the below query
To sum the score by ...
1
vote
0answers
22 views
PostgreSQL User-defined Aggregate stype is not always initialized properly [closed]
I've defined aggregate function with following stype
CREATE TYPE sumstglobal_contextcasttest_stype AS (a integer[],
"partResults" integer[],
"numberOfElements" integer,
...
1
vote
2answers
48 views
Group by multiple criteria
Given the table like
| userid | active | anonymous |
| 1 | t | f |
| 2 | f | f |
| 3 | f | t |
I need to get:
number of users
number of users ...
1
vote
2answers
76 views
Aggregate a single column in query with many columns
Is there a proper way to aggregate a single column when I have many other columns in the query?
I've tried this answer which works, but my query has become a lot more verbose.
My current query looks ...
0
votes
3answers
185 views
Concatenate multiple result rows of one column into one, group by another column [duplicate]
I'm sorry for the title, but I can't think of something better.
I'm having a table like this
Movie Actor
A 1
A 2
A 3
B 4
I want to get the name of a movie and ...
