Mysql – Select distinct then calculate a number

MySQLPHP

Structure of table:

user-----headline----day----number
1   -----lotr    -----1 -----  2
1   -----lotr    -----1 -----  2
1   -----cpo     -----2 -----  2
1   -----cpo     -----3 -----  6
2   -----ouch    -----1 -----  5

Results I am trying to get:

user-----headline-----number
1 -----    lotr  ----- **4**
1 -----    cpo   ----- **8**

echo
1 has lotr 4
1 has cpo 8

The query that I am trying to get is based on the user logged in and his id, in this case = 1.

I am trying like this, but no result:

SELECT DISTINCT headline, sum( number ) as result 
FROM table  
where user = '$login_session'"

echo 
result;

What is the best way to get these results and echo them?

I tried Rolando's query but nothing happened:

screen shot

Best Answer

The query you want is the following

SELECT headline,SUM(number) as sum_number
FROM table WHERE user = '$login_session'
GROUP BY headline;

You can make SQL echo the result by putting that query in a subquery

SELECT
    CONCAT('$login_session has ',headline,' ',sum_number,'</BR>') as result
FROM
(
    SELECT headline,SUM(number) as sum_number
    FROM table WHERE user = '$login_session'
    GROUP BY headline
) A;
Related Question