CODEWITHSUNDEEP
mysqljoin
ThinkCode
ThinkCode

Reputation: 7961

Find the result in one MySQL Query

#travel expense = select sum(val) from expenses where type = 'travel';

#food expense = select sum(val) from expenses where type = 'food';

#phone expense = select sum(val) from expenses where type = 'phone';

#total expense = select sum(val) from expenses;

How do I find a % of each expense in one query? Say $1000 total expenses and 50% of it is for food, 30% for travel and the rest for phone?

travel expense = (select sum(val) from expenses where type = 'travel')/(select sum(val) from expenses)*100 ==> What is the equivalent of this query in one query rather than two?

Upvotes: 0

Views: 63

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

select type, sum(val) / (select sum(val) from expenses) * 100 Percentage
from expenses
group by type

Result:

+--------+------------+
|  TYPE  | PERCENTAGE |
+--------+------------+
| food   | 17.7778    |
| other  | 20         |
| phone  | 35.5556    |
| travel | 26.6667    |
+--------+------------+

This assumes you want a percentage for all expenses in the table. If you have some other expenses you want to filter out then run this:

select type, sum(val) /
  (select sum(val) from expenses
   where type in ('travel', 'phone', 'food')) * 100 Percentage
from expenses
where type in ('travel', 'phone', 'food')
group by type

+--------+------------+
|  TYPE  | PERCENTAGE |
+--------+------------+
| food   | 22.2222    |
| phone  | 44.4444    |
| travel | 33.3333    |
+--------+------------+

Upvotes: 1

DRapp
DRapp

Reputation: 48139

select
      PreAggregate.TotalExpenses,
      PreAggregate.TotalTravel,
      PreAggregate.TotalTravel / PreAggregate.TotalExpenses as PctForTravel,
      PreAggregate.TotalFood,
      PreAggregate.TotalFood / PreAggregate.TotalExpenses as PctForFood,
      PreAggregate.TotalPhone,
      PreAggregate.TotalPhone / PreAggregate.TotalExpenses as PctForPhone,
      PreAggregate.TotalExpenses,
      PreAggregate.ExpenseItems
   from
      ( select 
              sum( if( type = 'travel', val, 0 )) as TotalTravel,
              sum( if( type = 'food', val, 0 )) as TotalFood,
              sum( if( type = 'phone', val, 0 )) as TotalPhone,
              sum( val ) as TotalExpenses,
              count(*) as ExpenseItems
           from
              expenses
           where
              type in ( 'travel', 'food', 'phone' ) ) PreAggregate

Upvotes: 0

James C
James C

Reputation: 14149

Can you do:

SELECT type, COUNT(*) FROM expenses GROUP BY type WITH ROLLUP;

...and pull it together yourself from there? It does provide all the data you need in a single query even if you have to do a little work outside the query.

Upvotes: 0

Marc B
Marc B

Reputation: 360572

Can't really do it in a "single" query, since you need both per-row and aggregate data to do this, but with some subqueries, it'll work:

SELECT ((
   SELECT SUM(val) FROM expenses WHERE type='travel'
) / (
   SELECT SUM(val) FROM expenses
)) AS pct

Upvotes: 1

Related Questions