CODEWITHSUNDEEP
mysql
KESAVAN PURUSOTHAMAN
KESAVAN PURUSOTHAMAN

Reputation: 453

How to select one field from view in MySQL?

How can do the following query in MySQL?

Select SUM(T1.Amount) 
From 
( 
  Select  Distinct PersonName,Amount 
  From  tblusers as T1 
  where city ="xxx"
)

Upvotes: 0

Views: 392

Answers (3)

Taryn
Taryn

Reputation: 247880

MySQL requires an alias on a derived table or subquery. So you will want to use the following:

Select SUM(t1.Amount) 
From 
( 
  Select Distinct PersonName, Amount 
  From  tblusers
  where city ="xxx"
) t1

You should be able to use the following though depending on your needs:

select personName, sum(Amount)
from tblusers
where city = "xxx"
group by personName

Or if you just want to return the sum, you can use:

select sum(Amount)
from tblusers
where city = "xxx"
group by personName

Upvotes: 1

KESAVAN PURUSOTHAMAN
KESAVAN PURUSOTHAMAN

Reputation: 453

I done it with below query.But wants to know is there any other possible way to do it.

    CREATE VIEW  T1 as (Select  Distinct PersonName,Amount From  tblusers  where city ="xxx" );

     Select SUM(Amount) From  T1

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

Just alias the subquery:

Select SUM(Amount) 
From (
    Select Distinct PersonName, Amount 
    From  tblusers 
     where city ="xxx")  t

And if you're looking for each person, add a GROUP BY:

Select PersonName, SUM(Amount) 
From (
    Select Distinct PersonName, Amount 
    From  tblusers 
     where city ="xxx")  t
Group By PersonName

If you really want the sum of each person (and not the sum of the distinct person/amount), then you don't need the subquery at all:

Select PersonName, SUM(Amount) 
From tblusers 
Where city ="xxx"
Group By PersonName

Depends on your actual needs though.

Upvotes: 2

Related Questions