I would like to give each player a rank / position based on the values of other columns, 1 being the best position. Each prize or penalty is worth a different amount. Can this be done in SQL and how would I go about putting this into a query? Here is the ratio that i… Read More How can I update a column based on a calculation of other columns in a table?

I am trying to separate table results into those who are present and absent. For example, I have this users table here and user_attendance table here. Those users in the user_attendance table were present and I am trying to use the query below to show those who are not present/absent in the user_attendance table: SELECT… Read More Separate list of present and absent in SQL table

I have an event table in which each user has several dates registered. The minimum date for each user would be the join_date for each user. I need to do a count for each user for each min date (join_date). I have achieved what I intend with the following query with a subquery in the… Read More How to count new users by min date without using a subquery?

For example, i have: string example = ‘word1,word2,word3,word4,word5’ How can i convert to something like this: string example = ‘[word1],[word2],[word3],[word4],[word5]’ >Solution : We can use a combination of REPLACE() and string concatenation: SELECT val, CONCAT(‘[‘, REPLACE(val, ‘,’, ‘],[‘) , ‘]’) AS output FROM yourTable; On MySQL 8+, we can use a regex replacement: SELECT val,… Read More How can add brackets to each word of a string which contains multiple words on SQL?

I have 2 tables: Table 1 "contract" is as the following (sample only): contract_id contract_status 111 Active 122 Active 133 Finished 144 Active 155 Finished contract_status values are: Active and Finished Table 2 "ledger" is as the following (sample only): ledger_id contract_id ledger_status amount 1gh 111 WAIVED 450 2uk 111 PAID 0 2jz 111 WAIVED… Read More How to query for amount waived for every contract status?

Query: SELECT ProductId,LanguageId,FileType FROM dbo.ProductFile GROUP BY ProductId,FileType,LanguageId Result: =============================== | Code | Language | File Type | ——————————- | 2040 | English | manual | | 2040 | English | exploded | | 2040 | English | catalog | | 2040 | Dutch | catalog | | 2041 | English | manual | |… Read More SQL query to show grouped rows in columns

user table: id = 1 name = David, id= 2 name = Jhon log table: user_id = 1 guest_id = 2 wanted output user_name = David guest_name = Jhon >Solution : SELECT T.NAME,T2.NAME FROM LOG_TABLE L JOIN USER_TABLE T ON L.USER_ID=T.ID JOIN USER_TABLE T2 ON L.guest_id =T2.ID You can use something like this

Alright this is my curiosity, my question is why… First this is the ERD to understand the tables and connections… Now my Query is supposed to "Provide a table that provides the region for each sales_rep along with their associated accounts. Your final table should include three columns: the region name, the sales rep name,… Read More SQL will output my query only if I alias my columns in my select statement… Not sure why

I have a table that contains pipeline jobs data. A pipeline is composed of many jobs that run independently, and each of them can finish at it’s own pace. Once the pipelines are finished, they are archived by setting one of the columns to 1. I want to get the list of jobs of the… Read More Optimizing query that selects on the result of a group by

I have a list of months formatted with the 3 letter abbreviation followed by the last 2 numbers of the year. For example, this current month would be SEP22. I need to find a way to turn this from a varchar into a date/datetime value, pointing to the first day of the month. So, if… Read More 3 Letter Month Abbreviation to Date