With ONLY_FULL_GROUP_BY set (which I don’t want to/can’t change) the following query errors with ERROR 1055 (42000): ‘dbname.test1.person_id’ isn’t in GROUP BY (or equivalent longer message in mariadb). How can I “tell” MySQL that I know the person_id is unambiguous b…
Tag: group-by
Group numbers by 45 in mysql
I have a database in which I store wind direction readings by degrees. I need to do two things really, i need to group all readings by 45 (basically 45 degrees between “N”, “NE”, “E”, “SE”, “S”,”SW”,”W” and “NW”) a…
SQL groups inside groups
I have transaction data like this example: Name | Price George | 20 George | 20 George | 20 George | 30 Paul | 20 Paul | 20 Paul | 30 Paul | 30 Paul | 35 I need to group by …
Group by issues with multiple columns
I am having trouble making a query work. I am selecting 5 columns, using 3 to filter and 1 to count: I keep getting an error that the other columns have to be grouped by or aggregated. I’m trying to get the total students per class per professor. I’m only selecting the other columns so I can filte…
MySQL group by week interval on Linux timestamps starting from Sunday
I have a table of users that have joined and the column that tracked the timestamp of when they joined is a UNIX timestamp. I want to group them by a weeks time in seconds, 604800, but am running into a roadblock. The other searches use MySQL week, but that is not what I am after since those weeks are
Get comments per video and total sum of comments per video channel
In addition to Get total sum of video comments for each video I noticed that I can’t use v.commentCount because I cleaned some data and now the column is inaccurate. So I need to count the videoId comments and the total videoID comments with the comment table. I have a table video and a table comments. …
Retrieve the first occurrence of a record by ID in SQL
I am trying to import some data into Excel from a SQL server, but I am having some issues. It’s my first time writing a query, although I am able to get the entire dataset, I have realized that the data is full of duplicates. I want to get rid of duplicates in Excel and only keep Unique records. I
Postgresql Select and display one column based on another column
WALLET TABLE ID IS_SPEND HOWMUCH true. 500 false. 1000 true. 5 I want to calculate how much money I have after several transactions. spend as spend money and not spend as earn money I tried But it cannot reach my goal. I don’t know how can I get the result I want, just output 495 is perfect. Answer You …
How to de-duplicate SQL table rows by multiple columns with hierarchy?
I have a table with multiple records for each patient. My end goal is a table that is 1-to-1 between Patient_id and Value. I would like to de-duplicate (in respect to patient_id) my rows based on “a hierarchical series of aggregate functions” (if someone has a better way to phrase this, I’d …
SQL: join on foreign key between two tables and return only count of items; not actual items
I know there’s lots of questions similar to this out there. I’m writing my first SQL query; and I’ve been looking through SO but I can’t find out specifically what I’m doing wrong, because to me, they …