Adding MySQL Alias Fields Together

MySql - use of ALIAS for multiple columns

You can't use the same alias for multiple columns, but you can concatenate values and give the result an alias:

SELECT 
  u.ID, u.username, u.active, u.email, u.admin, u.banned, 
  u.name + u.username AS groupmemberships 
FROM users u 

If this is what you want, then check here for how to deal with null values.

Adding up Alias with other MySQL column value

What you could do is this:

select x.*, ((x.total + rec_host) / 2) AS total2
from (

SELECT host_name, rec_host,
       SUM(rank_ur) AS cnt1, 
       SUM(rank_scs) AS cnt2,
       SUM(rank_tsk) AS cnt3,
       SUM(rank_csb) AS cnt4,
       SUM(rank_vfm) AS cnt5,
       SUM(rank_orr) AS cnt6,
       SUM(IF(rec_host = 1,1,0)) AS rh1,
       SUM(IF(rec_host = 0,1,0)) AS rh2,
       ((rank_ur + rank_scs + rank_tsk + rank_csb + rank_vfm + rank_orr) / 6) AS total
   FROM lhr_reviews 
   GROUP BY host_name
   ORDER BY total 
   DESC LIMIT 0,10
) as x
;

You cannot use the column as an alias when the alias and other column are in the same level of SELECT. So you can use a derived query which lets you basically rename your columns and/or name any computed columns.Check on Rubens Farias and Rob Van Dam answer here

PS: will search for a better article to update the answer :)

Alias for column name on a SELECT * join

It looks like you have to alias you'r column names with aliases.

SELECT client.column1 as col1, client.column2 as col2, person.column1 as colp1 FROM client INNER JOIN person ON client.personID = person.personID

Of course, replace the column names into the real column names as use more appealing aliases

Let us know if it helps

UPDATE #1

I tried creating 2 tables with sqlfiddle in mySQL 5.5 and 5.6

see link : http://sqlfiddle.com/#!9/e70ab/1

It works as expected.

Maybe you could share you tables schema.

Here's the example code :

CREATE TABLE Person
(
personID int,
  name varchar(255)
);

CREATE TABLE Client
(
ID int,
  name varchar(255),
personID int
);

insert into Person values(1, 'person1');
insert into Person values(2, 'person2');
insert into Person values(3, 'person3');

insert into Client values(1, 'client1', 1);
insert into Client values(2, 'client2', 1);
insert into Client values(3, 'client1', 1);

SELECT * FROM client 
INNER JOIN person 
ON client.personID = person.personID;

MySQL INSERT with table alias

Use back-tick to escape reserved words.

  INSERT INTO `attributeStrings` (`ItemID`, `Key`,`Value`) VALUES (3,'Categories','TechGUI')

Looks like insert does not support alias. see here

---

Edit: ok, the MySQL ref says no alias in insert

---

It does work

mysql> INSERT INTO `attributeStrings` (`ItemID`, `Key`,`Value`) VALUES (3,'Categories','TechGUI');
Query OK, 1 row affected (0.03 sec)

mysql> select * from attributeStrings;
+--------+------------+---------+
| ItemId | Key        | Value   |
+--------+------------+---------+
|      3 | Categories | TechGUI |
+--------+------------+---------+
1 row in set (0.00 sec)

---

Related Topics