I have a chats table and chat_reactions table. Each chat message can have many reactions and a reaction is a text and it can be many. I am trying to return messages with the grouped reactions and the total number of times a particular reaction is used. For example, msg: hi with id 1 got a total of three react…
SQL query, counting IP-Adress (Grafana, DMARC)
Maybe this question exists, but I couldt find it so you can also just link me to a similar question. My problem: I have a database and wand to visualize it with Grafana. In Grafana you have the option to create a query in SQL notation. In my case I want to count the similar IP addresses I have in
Sql server Balance Table Update Recursive
I Have a SQL Server database with a table named Balance The table: Id Date IN OUT Balance 3345312 2022-08-07 100 50 250 5435245 2022-08-06 50 50 200 4353451 2022-08-05 0 100 200 5762454 2022-08-04 20 100 300 7634523 2022-08-03 400 100 380 5623456 2022-08-02 100 20 80 4524354 2022-08-01 0 0 0 Id = Unique Ident…
Complex SQL Query For Counting and Listing
I need to write complex sql for counting distinct names popularity and sorting them with descending order. I’ll do this with hibernate @Query(“SELECT * FROM …”) parameter. First I tried to do it on SQL then implement it to my spring boot project but I’m stuck while writing it. So…
Target table cannot be same one for update clause in mysql
I have a table name named ’employee’. Table creation code given below: The table content is like below: Now I want to update ‘e_id’, first it will check whether the same e_id is in the table anywhere or not, if it is not in the table then only it will update the rows with given e_id, e…
Number of Customer Purchases in Their First Month
I have a list of customer orders. I can easily calculate the month and year of first purchase for each customer (e.g. customer 1 had their first purchase in Sept 2021, customer 2 had their first purchase in Oct 2021, etc.). What I want to add is an additional column that counts the number of purchases a custo…
How do I solve the error Ambiguous column name with join
I’m currently struggling with a join where the column [NAME] results in an error (ambiguous column). I’m pretty new at SQL and someone else made the first join for me, so I now just tried to replicate this with something else I wanted to join and as I understood, I do need to add the columnname of…
How to concatenate column values with matching column values in another column inside a case statement?
I would like to create hhID in my column based on values in NodeID. IF nodeID is empty, hhId should be a copy of UID, If not, then hhID should be a combination of all UIDs with the same values of nodeID.I have put dummy text here; ‘Nepal is there’. However, for UID, ‘JBGNARZ1’, hhID sh…
How to get only first row grouped by multiple columns?
I want to select a few columns, but only keep the first row grouped by the columns. If I use It can give me two rows for one item_no, if item_type and item_name don’t match in the two rows. sample output: How do I make sure I only get the first row grouped by three columns? Expected output: I’m us…
PostgreSQL: Return gaps and value mismatches between columns
Imagine the following data: I am interested in returning the entries where there is a gap in the data for a particular student, where the category changes from one year to another, or both. So the output that I’m after is: The reason why id 4 would not be returned is because there is actually no gap or …