I have a table generated from a CRM software and it has a lot of repited individuals in different rows but with different fields completed in each repetition, something like this: id birth_date sex postal_code customer smoker 001 NULL NULL 00067 Yes 1 001 NULL Male 00067 NULL 1 001 21/03/1994 NULL 00067 NULL NULL 002 NULL Female NULL NULL
Tag: distinct
Get distinct values within time chunks
I have a table with different species and their occurences (timestamp) from field survey: Now I want to count them, not in total but in chunks of 10 minutes each, where only the first occurence is counted. That means between 12:31 and 12:40 there are species A,B,C. Between 12:41 and 12:50 there are species B,C. For a single 10 minute
Possible to select only 1 row for usernames, even though multiple rows which include usernames are unique
I am trying to select all usernames that have a at least one windows operating system. Some users have many windows operating systems, but I really only need to know if they have at least one. I know DISTINCT can filter out duplicates, but the issue is these rows are unique, if they have more than one OS. For example:
SQL Finding Dupe Names with Distinct ID
I have a list of customers, and a bunch of them are duplicates (‘Acme Inc’, ‘Acme, Inc’, ‘Acme Inc.’, ‘Acme, Inc.’) They all have different IDs. BUT, each ID also has multiple addresses. Something like… I have a function that normalizes the name so the first 6 would all be ‘Acme’ and the last two ‘Plumbers’. What I want is
PostgreSQL join table on json property and get oldest first from result with nulls first
I got 2 tables; domains and events. Im trying to create a query that returns a list of distinct domains that is ordered by the oldest events (for that domain) with nulls first and distinct domain. Basically this query will do the job: But the output is not distinct on ‘domain’. When using ‘distinct on’, it gives the wrong output
How to use multiple count distinct in the same query with other columns in Druid SQL?
I’m trying to use three projections in same query like below in a Druid environment: But instead I get an error saying – Unknown exception / Cannot build plan for query It seems to work perfectly fine when I put just one count(distinct) in the query. How can this be resolved? Answer As the Druid documentation points out: COUNT(DISTINCT expr)
SUM aggregation do not work on empty subqueries with DRUID
Assuming I am executing the following two queries on Druid: I tried to combine both counts in one single query with a sum aggregation like the following: And it works well UNLESS one of the two counts returns 0 rows. In that case I receive a Query returned no data message and I don’t have any final result from the
Calculate distinct values per day that resets each month (Big Query)
If I have table like this that contains unique id of a store and the date when they make an order store_id order_date A 01-Jun-21 A 02-Jun-21 B 02-Jun-21 C 02-Jun-21 A 03-Jun-21 A 04-Jun-21 D 04-Jun-21 A 01-Jul-21 B 01-Jul-21 I need to aggregate it by day, but each day should only consist of store_id that never appeared before
DISTINCT and ORDER BY in the same command without using the order by variable
I need to select the recent 5 games the user has gone into but I don’t want there to be duplicates. So I need a SQL line that will take them in order from most recent to least but also make sure there are no distinct. To order them I have a time variable, but if I use: this removes
Laravel Eloquent Equivalent for MYSQL DISTINCT query multiple columns
Is there an equivalent query for this in eloquent? Answer Try something like this: