I am trying to figure out how to write a query to do this. I have two ID’s in the table ID1 and ID2. Below is what I want. Want ID2 has count(ID2)>1 count of distinct ID1 >1 I am not sure how to do this with Oracle SQL. I did write code to do the group by. However, I
Tag: group-by
MySQL:How to Rewrite a Select element as ‘y’ then using that later in a where statement
So what im trying to do is this but this doesn’t work: Here is another example: Answer You can’t use an aggregate expression (sum(), count(), …) in the where clause of the query. This clause is evaluated before rows are grouped together by the group by clause, so the aggregates are not yet a…
In the SQL Query I couldn’t get truly data
Hi, I have a facilities table. I want to know which hotel_id has 2 facilities at the same time. with that no record. Answer Your query does not work because you are looking for rows that satisify both conditions simultaneously, which cannot happen. You need to look across rows of the same group. Instead, you …
How do I get IDs associated with the most frequent value in PostgreSQL?
I have following data in my PostgreSQL 11 database count_tbl (Windows 10 x64 machine). The following query, for each group (grp), finds the most frequent value: The output of above query is: Now, I would like to associate the most frequent value to the corresponding IDs (for example, for grp = 1, most frequen…
Dynamic bit-based flattening of multiple rows by pivoting into additional columns
I have data that looks like this: And I would like it to look like this: I think a dynamic conditional aggregation is required. Any help would be appreciated. Answer Here is a sample implementation of dynamic conditional aggregation: this is the result: Now you can add a value (for example id = 4 and value = …
MS SQL Find a Value in entire column and return true if found
New to SQL trying to find how to do the following : I have a ProductID# ABC123 (Product) I need to check the “workflow” (Workflow) column and see if the Product ID was ever “extended” – there are various workflow steps – Need to check the entire workflow column, if a Produc…
Group by to include case statement inside SQL statement
I have a table which stores purchase info from sellers and table contains rating to every purchase out of 5 stars. I want to have output Group By sellers and Each sellers good(Above 3) and bad(Below 4)…
Postgres, aggregate result to a single row using values from a column
In Postgres, I am trying to aggregate results from a table that looks like this: id restaurant_id title category ————————————————- 1 2 …
Duplicate rows that belong to multiple groups before group by
Let’s say I have a table of customers and I want to group them depending on their first and last name and then get the mean age of each group. However, my rules are not mutually exclusive. For …
Query for datediff between rows where value was unchanged, grouped by the value
I am looking to query an invoices table which also keeps track of which “program” customers were on at the time of their purchases (not the best way of storing that but it is what it is and I’m left …