I have the table and now I want to list every ProjectName where there is no specified Leader like this: Right now I only know how to filter all ProjectNames with Leaders, but not the way to filter them the other way! Any help is appreciated 🙂 Answer One way to do it is with aggregation and the condition in
Tag: group-by
How to get the first row per group?
I have a query like this: The result looks like this: Now I want to get the first row for each category_id. So it must be the biggest num and its business_id. So the expected result would be: How can I do that? Answer if your MySQL version support ROW_NUMBER + window function, you can try to use ROW_NUMBER to
Finding duplicate values in multiple colums in a SQL table and count
I have the following table structure: I use the following command to display the duplicates and the counts: This command gives me a count of 2. I would like to know how the second line could also be counted as a dublicate. Answer You need the scalar functions MIN() and MAX() to get the 3 integer values of each row
How to compare multiple rows
I have a table with data like following, want to return those group_id with unique data. Both group_id 3 and 4 have two component 123 and 456, so they are “duplicated”, we just need to return the smaller group_id, that’s 3. Also group_id 5 doesn’t have a duplication, it can be returned. So we want group_id 3 and 5 to
How to select rows from table when a column repeats?
I have this table (REGIONS) with this sample values: Id Cod1 Cod2 Payed 1 0001 000A NULL 2 0001 000B YES 3 0001 000B YES 4 0001 000C NULL 5 0001 000C YELL 6 0001 000D NULL 7 0002 000A YES 8 0002 000C NULL 9 0002 000C NULL 10 0002 000C YES 11 0003 000C YES 12 0004 000C
How to select data with percentages from Clickhouse?
Given the following table: I’m trying to fetch all statuses by host, with their relative percentage. In order to do that, I need to count results grouped by host and status, and divide each counted field by its total host fields count. For example, this query would have worked in MySQL: But ClickHouse throws: DB::Exception: Unknown identifier: host_total; there are
SQL group by return null if one is null
I have orders table that have delete marker column date_deleted which value is null or date order_number item_id date_deleted 111 aaa null 111 bbb null 111 ccc 2021-x-x I have query to select the fully deleted orders with group by order_number It give me this result order_number date_deleted 111 2021-x-x Which indicate that the whole order is deleted but is
LEFT JOIN ON NULL Key Values Combined with GROUP BY
I’m using Teradata SQL and I wrote the following query (pay attention at the LEFT JOIN) Some entries for t1.key2 und t1.key3 (of the left sided table) are NULL. When that’s the case, the rows are not showing in the result, why? Is that Teradata specific, I would expect a LEFT JOIN to show rows with NULL values. If I
Join count query into one
I have two very simple count queries that I would like to join into one. To clarify the situation I will add the tables I got: Book isbn author 1111 GB 2222 DC 3333 RL Author code Name GB George B. KL Kyle L. DC Donald C. RL Roland L. Coauthor name isbn KL 1111 GB 2222 GB 3333 And
Delete all SQL rows except one for a Group
I have a table like this: Schema (MySQL v5.7) id userid date 1 1 2021-11-15 2 2 2021-11-15 3 1 2021-11-13 4 3 2021-10-13 5 3 2021-09-13 6 2 2021-09-13 View on DB Fiddle I want to delete all records which are older than 14 days, EXCEPT if the user only has records which are older – than keep the