DELIMITER $$ USE `db_sp`$$ DROP FUNCTION IF EXISTS `bil`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `bil`(num INT) RETURNS VARCHAR(7) CHARSET latin1 BEGIN DECLARE result VARCHAR(7); CASE num WHEN num > 0 THEN SET result = "Positive"; WHEN num < 0 THEN SET result…
I have two tables like this: Table A id sum_value city 1 14 Paris 1 4 Paris 2 10 Berlin 3 68 Milan 4 51 London 3 2 Milan Table B id city geom 1 Paris MULTIPOLYGON(XXX) 2 Berlin MULTIPOLYGON(XXX)…
I have two functions where the first one is supposed to return the records for this month and the second one is suppsoed to return the records from last month, but they are both returning the records for this month.…
We (almost) always add a general id column as a primary key while designing sql tables. If a table already contains a unique column, I wonder if we should designate that as the primary key and not use the id…
I have a table having below schema Item:Varchar Date:Date Quantity:Float trasactionid:int Sample Data: Item Date Quantity transactionid Part1 01-01-2023 10 3 Part1 01-01-2023 15 5 Part1 01-01-2023 17 2 Part1 01-01-2023 13 6 Part1 02-01-2023 13 7 Part1 02-01-2023 1…
i have table like this (each row represents an order): order_id date customer_id 11 '2023-01-01 00:03:43' 123 12 '2023-02-01 00:02:43' 456 13 '2023-02-01 00:03:44' 789 14 '2023-04-01 00:03:43' 1230 i tried this: SELECT DATE_FORMAT(date,'%Y-%m') , COUNT(distinct customer_id) as count FROM…
I have a mysql table named 'access' with 2 fields: 'owner_id' and 'date_created_id'. I have another table named 'usersx' with 2 fields: 'id', and 'last_name'. I would like to use a single query to get both last names from the…
I'm trying to join data together, my goal is to have list of sellers, and see next to each seller, the median time it took him to sell his items. The following query works as expected on a single seller…
I have 2 tables: availability and blocked_availability. availability: day start end 1 09:00 11:00 1 12:00 16:00 blocked_availabilty day start end 1 09:30 10:30 1 12:30 14:30 From these 2 tables combined I would like to see if there's a…
I have the following sql query that is returning time spent logged in for users select sum(duration) as time_spent, date_trunc('day', date)::date as date from user_activities where name = 'session' and user_id = '#{user_id}' group by date order by 2 It's…