I have been trying to query each city’s popular genre. I am only trying to get the rows that I have highlighted. I tried using MAX() on a group by but gave me a syntax error. My CTE query is as follows, its based on the dbeaver sample dataset: I tried the following query. Answer I don’t have a dataset
Tag: group-by
SQL group by a field and only return one joined row for each grouping
Table data What I want to do is group by the name, then for each group choose one of the rows with the earliest required by date. For this data set, I would like to end up with either rows 1 and 4, or rows 2 and 4. Expected result: OR I have something that returns 1,2 and 4 but
SQL Group by Transpose
I have the following Table charges Using the following query gets my the counts by Charge and Date Is there a way to transpose this to get the following? Thank you. Answer Use conditional aggregation:
SQL Server Sum specific transaction type amounts
I have a temp table (#general) that contains Account number, transaction type, Amount info as below AccountNumber Tran_Type Credit 96551AQ6 TSCNYAEUQF 0.0000000 96551AQ6 TSCNYAEUQF …
min and count without limit
I need to select a receipt with 3 conditions: total receipt > 50 (receipt.total) associated purchases doesn’t include item “cucumbers” (product.pname) the number of suppliers (product.sid) of associated products is the lowest I have the following tables set up: sample data: So far I have the following but it is not getting the receipt with the lowest number of sid:
multi condition on different rows
age | name | course | score _________________________ 10 |James | Math | 10 10 |James | Lab | 15 12 |Oliver | Math | 15 13 |William | Lab | 13 I want select record where math >= 10 and lab >11 I write this query but this query does not return any record. I want this result age
Conditional Grouping in SQL
This should be easy, but I’m having trouble with it. I have a results table that looks like this: Notice that the pct column sums to 1.0 for each store. Here’s the code to create this table: I’m trying to group the results by store, then by cust_id while creating a new category A&B if the same cust_id has both
Create a column based on conditions in select statement
I have an order table in Postgres with order number and status as column. If a new order is placed the default status were Order placed and Goods Assigned. order 12345 order 543223 I want to know if the order were out from the sender place. So I want to create a column called started and has value Yes if
Group rows on condition PostgreSQL
I have a table with repeated Column1 and Column2 pairs, let’s say this is ‘many-to-many’ table. Also, I have there one extra integer column – Column3. What I want is select conditionally grouped rows, like 1). If pair of Column1 and Column2 have several records contains 5 value among others – then it should be grouped into one row with
Can I Select DISTINCT on 2 columns and Sum grouped by 1 column in one query?
Is it possible to write one query, where I would group by 2 columns in a table to get the count of total members plus get a sum of one column in that same table, but grouped by one column? For example, the data looks like this I want to get a count on distinct combinations of columns “OHID” and