If' in 'Select' Statement - Choose Output Value Based on Column Values

IF' in 'SELECT' statement - choose output value based on column values

SELECT id, 
       IF(type = 'P', amount, amount * -1) as amount
FROM report

See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html.

Additionally, you could handle when the condition is null. In the case of a null amount:

SELECT id, 
       IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
FROM report

The part IFNULL(amount,0) means when amount is not null return amount else return 0.

IF' in 'FROM' statement - choose table value based on column value

SET @var = (SELECT CI FROM users WHERE something);

SELECT
    CASE @var
        WHEN 'pc' THEN
            (SELECT name FROM table1 WHERE something)
        WHEN 'ph' THEN
            (SELECT name FROM table2 WHERE something)
    END;

SELECT statement with a new column based on a value

Use conditional aggregation:

select month, id,
       sum(case when year = 2001 then amount else 0 end) as amount_2001,
       sum(case when year = 2002 then amount else 0 end) as amount_2002,
       . . . 
from t
group by month, id;

SQL Query for applying a column value based on the Min and Max

You know you need to refer to the min and max values on a date basis to accomplish this. I expect to see that attempt in your code. You can use MIN and MAX as simple aggregates (old way) or as analytical functions (newer way).

The old way uses a traditional aggregation in a CTE (or derived table) and simply joins the rows of the table to the CTE. That allows you to compare the punch value with the min and max to know if the row represents the first "in" or the last "out". I took some liberties with that logic and made assumptions. Do you see them? Are they appropriate? Can someone leave without an "out" row? Know your data!

with minmax as (
   select emp_code, trandate, 
      min(punch_time) as first_in, 
      max(punch_time) as last_out
   from @x 
   group by emp_code, trandate 
)
select clocktran.*, minmax.first_in, minmax.last_out, 
       case when clocktran.punch_time = minmax.first_in then 'Time In'
       when clocktran.punch_time = minmax.last_out then 'Time Out'
       when clocktran.punch_state = 1 then 'Break out'
       else 'Break in' end as Status 
from @x as clocktran inner join minmax 
   on clocktran.emp_code = minmax.emp_code
  and clocktran.trandate = minmax.trandate 
where clocktran.emp_code = 1 and clocktran.trandate = '20220607'
order by clocktran.punch_time
;

fiddle to demonstrate. At the bottom of the script is another statement using the analytical versions of min/max. I leave it to you to adapt that to a complete solution. Use of row_number is another (but less obvious and slightly more complex) technique that can be applied. Much depends on the consistency and quality of your transaction data.

How to update value of a column based on sum of same column values of other records in MYSQL?

Mysql multi table update manual page - https://dev.mysql.com/doc/refman/8.0/en/update.html

eg

drop table if exists t;

CREATE TABLE T(ID INT,SUBJECT VARCHAR(10), MARK INT);

INSERT INTO T VALUES
(1,'ALL',NULL),(1,'AAA',1),(1,'BBB',2),
(2,'ALL',NULL),(2,'AAA',10),(2,'BBB',20);

UPDATE T 
JOIN
 (SELECT ID,SUM(MARK) MARK FROM T WHERE SUBJECT <> 'ALL' GROUP BY ID) S ON S.ID = T.ID
SET T.MARK = S.MARK
WHERE SUBJECT = 'ALL';

Conditional select based on column value

You could use some nested analytics, though this looks a bit more complicated than it probably should:

select id, type, client_id
from (
  select t.*,
    case when type = 'a'then 1
      when type = 'b' and c_count = 0 then 2
      when type = 'c' then 3
    end as rnk
  from (
    select f.*,
      sum(case when type = 'a' then 1 else 0 end)
        over (partition by client_id) as a_count,
      sum(case when type = 'b' then 1 else 0 end)
        over (partition by client_id) as b_count,
      sum(case when type = 'c' then 1 else 0 end)
        over (partition by client_id) as c_count
    from files f
  ) t
)
order by rnk;

SQL Fiddle showing how that builds up to the final result.

Or maybe a bit better, and this time only pulling a single record which I think is the end goal inside a loop (?):

select id, type, client_id
from (
  select t.*,
    dense_rank() over (
      order by case when type = 'a' then 1
        when type = 'b' and c_count = 0 then 2
        when type = 'c' then 3
      end, client_id) as rnk
  from (
    select f.*,
      sum(case when type = 'c' then 1 else 0 end)
        over (partition by client_id) as c_count
    from files f
  ) t
)
where rnk = 1;

Updated SQL Fiddle, showing working again, so you can see the evaluated order is what you asked for.

Either way this only hits the table once, which may be an advantage, but has to scan the whole thing, which may not...

Conditional WHERE in SQL query based on column value

I found the answer! /p>

SELECT * 
FROM table_name
WHERE UserId = ? 
AND ((IsPrivate = true and Status = "Done") OR IsPrivate = false)

---

Related Topics