Sql Sum question

Question

In this code

SELECT SUM(field_name) AS total FROM tablename;

what does that "AS" means ?

Answer 1

Assoc mean that you will access to your table row with a name (total here). The other way to access to a row is with the index of the row (0, 1, 2, ...).

Answer 2

@Gerge Not all row, in this case, it put the sum in one row and it call the row "total". With sum (xxx) you only have 1 row for result.
If you do :
SELECT name AS n, age AS a FROM TABLE;
you will have 2 rows for result. One called "n" and the other "a".

Answer 3

With aggregate SQL function (like min, max, sum, avg,...), if you don't fix the name with an alias, the DB driver will give one for you. So it can change depending on the DB driver. With an alias, you are sure on the name.

Answer 4

I will explain you what the line do. I hope it will help you.

$result=$conn->query("select SUM(Bill) as total from tbl_reservation");
With your connexion (conn) you execute a query and put the result set in a variable (result). This variable contains a lot of informations and is not directly workable.

$row=mysqli_fetch_assoc($result);
You transform your result into an associative array (with 1 line and 1 row called "total" in your case) which is more easier workable.

$sum =$row['total'];
You extract the row called "total" and put the value in the variable sum.

Answer 5

The sum will be in a row called "total". It is an alias.

Answer 6

In SQL, the results are like a tabular, with rows and lines. The rows have a name. You can fix this name with the AS.

Answer 7

So yes, your data will be accessible with "total" name.

Answer 8

Yes. If you don't name it (remove "AS total"), the name will be fixed by the DB driver.

Answer 9

Which is your PHP version?

Answer 10

This code give you only the sum. And the sum has the alias total. You don't have any detail with this code.

Answer 11

ah thank you I understand now

Answer 12

and I am the one who will name it ?

Answer 13

what will happen when I just echo $result directly and dont use alias ?

Answer 14

but this code is getting all the rows in the column Bill and gets the sum ....correct me if Im wrong ... so which row has the alias total ... is it all rows ?

Answer 15

the result will be stored in "total" ??

Answer 16

I have another question connected to this

this is the whole code with php

$result=$conn->query("select SUM(Bill) as total from tbl_reservation");
$row=mysqli_fetch_assoc($result);
$sum =$row['total'];

my question is why do I have to use assoc? and what it means ? I'm sorry I'm new to php

Answer 17

@Geo Do you mean that ‘AS’ creates alias/ gives name to the column where all the rows from the result are being stored?

Answer 18

@Geo correct the sum is in one row, though, that row is in the column “total”

The second case you pointed out are two columns “n” and “a” filled with the rows resulted from the query.

it is SELECT column1,column2,... from tablename where.....