On MySQL 8+, we can handle this without a subquery by using analytic functions: SELECT b.id, a.invoice, b.price AS total, SUM(b.price) OVER (PARTITION BY a.id) AS count FROM TableB b LEFT JOIN TableA a ON a.id = b.aid; On earlier versions of MySQL, or if you are doing this from an ORM layer which doesn’t … Read more
AS some of your logstep_* columns value is NULL so try like this with ifnull, I’ve added only three columns you can try with all of your columns. Hope this will help you. select SEC_TO_TIME( UNIX_TIMESTAMP(ifnull(logstep_1, 0)) + UNIX_TIMESTAMP(ifnull(logstep_2, 0)) + UNIX_TIMESTAMP(ifnull(logstep_3, 0)) ) as logstep_sum_in_time from log_step 2 solved How to SUM Datetime per … Read more
CREATE FUNCTION get_id(NAME IN Courses.NAME%TYPE ,COURSE_ID_O[] OUT ARRAY ) RETURN COURSE_ID_O BEGIN IF NAME LIKE ‘_J%’ OR NAME LIKE ‘_Z%’ THEN SELECT COURSE_ID INTO COURSE_ID_O FROM COURSES WHERE COURSE_NAME=NAME ; ELIF NAME=” OR NAME=NULL THEN DBMS_OUTPUT.PUT_LINE(‘Please enter a valid string.’); END IF EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘No record found If it is blank or null: … Read more
You can group by building, location for the rows where object in (‘WALL’, ‘WINDOW’): select building, location, ‘FLAG’ action from tablename where object in (‘WALL’, ‘WINDOW’) group by building, location having count(distinct object) < 2 The condition count(distinct object) < 2 in the having clause returns combination of building, location where ‘WALL’ and ‘WINDOW’ do … Read more
Your description of what you want for the desired the result is not exactly clear but it seems like you can use the following to get the result. This get the total number of rows per username on each of the previous 5 days (based on the max date) as well as the total number … Read more