Mysql tips

1. IF statement

I have this

table: tbl_1Type     Value
x              1
x              2
y              1
z              0
x              3
y              1

 

I’d like to get the average of Value when Type = x in one sql query.

I tried this: SELECT IF(Type=’x’, AVG(Value), 0) FROM tbl_1

The result I want is: 2   [= (1+2+3)/3]

SELECT
AVG(IF(Type=’x’, Value, NULL))
SUM(IF(Type=’y’, Value, NULL))
FROM tbl_1;

2. Regular expression

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

WHERE A.account_id REGEXP (IF (Fd.reading_limit_flg=1, ‘[[:digit:]]+’,
(SELECT A.account_id FROM Account A WHERE user_code = ?)))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: