how to make it as one?

Question

rechargedate |   mobile       |   amount
-------------+----------------+--------
01-07-2009   |   0103001199   |   50.00
01-07-2009   |   0103001199   |   50.00
20-07-2009   |   0103001199   |   50.00

Above is a part of my data, now I want to make recharges of the same number on the same day become one row:

rechargedate  |  mobile       |  Amount
--------------+---------------+--------
01-07-2009    |  0103001199   |  50.00
20-07-2009    |  0103001199   |  20.00

How can I do that (convert? cast?)?

Answer 1

SELECT DISTINCT columns FROM table WHERE .....

Answer 2

Maybe you're looking for distinct?

Answer 3

SELECT DISTINCT columns FROM table GROUP BY columns

looks like you'll be needing GROUP BY as well

Answer 4

SELECT rechargedate, mobile, SUM(amount) FROM tablename
GROUP BY rechargedate, mobile

Answer 5

nope.. i actually already got the group by statement.. but i nw want to know how to make it bcum conver or cast into one based on the datetime

Answer 6

SELECT a.rechargedate, a.mobileno,sum(a.Amount),b.reg_dealer, 
(c.agn_code + '-' + c.agn_master_code + '-' + c.agn_state + '-' + c.agn_dealer_code), b.reg_activationdate
from recharge a, dailyregistration b, agents c
where 
substring(b.reg_dealer, 5, 6) = c.agn_dealer_code
and b.reg_id = a.mobileno
and month(a.rechargedate) = 7
group by a.rechargedate, a.mobileno, b.reg_dealer, (c.agn_code + '-' + c.agn_master_code + '-' + c.agn_state + '-' + c.agn_dealer_code), b.reg_activationdate
order by a.mobileno

Answer 7

If the two rows are identical, you can use the DISTINCT keyword right after SELECT:

select DISTINCT rechargedate, mobile, amount FROM yourTable

This will not work if you have rows such as:

rechargedate |   mobile       |   amount
-------------+----------------+--------
01-07-2009   |   0103001199   |  150.00
01-07-2009   |   0103001199   |   50.00
20-07-2009   |   0103001199   |   50.00

In that case you need to GROUP BY the rechargedate. This will fetch only one row for each different rechargedate, which might not always be what you need:

select rechargedate, mobile, amount FROM yourTable GROUP BY rechargedate