Duplicate Data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
outlaw
Forum Newbie
Posts: 9
Joined: Thu May 19, 2005 2:23 pm
Location: South Africa

Duplicate Data

Post by outlaw »

can anyone tell me how i can get data from mysql eg

outlaw linux php 10
outlaw linux php 11
linexus linux php 12
linexus linux php 13
linexus linux php 14

how can i show only one line but add up the numbers at the end of the line
to get a total for each user so the output would be

outlaw linux php 21
linexus linux php 39

these lines are selected between two dates.so i have to display the details week for week and add up the totals

pleaaase help very desperate?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

it's obvious it's not a very sane design to have only one column where you might have wanted at least 2. (one for the text, one for the number).

that way you could have easily done:

Code: Select all

SELECT text, SUM(number)
FROM table
GROUPBY text
Now you will have to look at the string functions of your dbms to extract the numbers from each value...
User avatar
outlaw
Forum Newbie
Posts: 9
Joined: Thu May 19, 2005 2:23 pm
Location: South Africa

Post by outlaw »

timvw wrote:it's obvious it's not a very sane design to have only one column where you might have wanted at least 2. (one for the text, one for the number).

that way you could have easily done:

Code: Select all

SELECT text, SUM(number)
FROM table
GROUPBY text
Now you will have to look at the string functions of your dbms to extract the numbers from each value...
thats an idea, the reason why i put it in one table is because it is a timesheet. But i will look into that idea. I have added up the numbers in another script and i just need the total an it works fine but this has to be done user for user.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

untested, the following code might be another source for inspiration ;) (so start reading your dbms manual for the actual functions.. and their parameters)

Code: Select all

SELECT SUM(CONVERT_INTEGER(SUBSTRING(text, -2, 2))) AS sum, SUBSTRING(text, 0, -2) AS text
FROM table
GROUP BY text
User avatar
outlaw
Forum Newbie
Posts: 9
Joined: Thu May 19, 2005 2:23 pm
Location: South Africa

Post by outlaw »

Thanks will try and let you know...thanks again.
User avatar
outlaw
Forum Newbie
Posts: 9
Joined: Thu May 19, 2005 2:23 pm
Location: South Africa

Post by outlaw »

Hi there i got it to display only 1 line per user but i dont know how to
add up the numbers.

this is my query i use, should i change it?

Code: Select all

$query=" SELECT * from $tbl1 where $fld1 
between '$dfrom' and '$dto' group by $fld2";
User avatar
outlaw
Forum Newbie
Posts: 9
Joined: Thu May 19, 2005 2:23 pm
Location: South Africa

Post by outlaw »

outlaw wrote:Hi there i got it to display only 1 line per user but i dont know how to
add up the numbers.

this is my query i use, should i change it?

Code: Select all

$query=" SELECT * from $tbl1 where $fld1 
between '$dfrom' and '$dto' group by $fld2";
CAN ANYONE HELP ME PLEASE????????????????????? :?:
Post Reply