1

I have created 4 tables(Mumbai, Bangalore, Pune, Delhi) with customer information

In all 4 tables, I have a bill amount table column in my table, I want to display the sum of bill Amounts of all tables from Highest to lowest.

I have used the following query, but I don’t know how to go ahead with it.

Create view view1 as: 

Select sum(m.BillAmount),sum(p.BillAmount),sum(b.BillAmount),sum(c.BillAmount)
from Mumbai m,Chennai c, Pune p, Bangalore b 
ORDER BY

Please can I get appropriate queries related to this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Glen Veigas
  • 85
  • 1
  • 12

1 Answers1

1

You have a design issue here. You should have only one table to store all your cities. You have now 4 cities, but if tour client wants to add another, with your current design you will have to add a table and change your sql queries.

If you create a table for your cities and another for your bills with a foreign key referencing the cities, you will be able to get the sum of your bills for all of your cities. 

Table cities
------------------
id     city_name
1      Mumbai
2      Bengalore
3      Pune
4      Delhi
-------------------

Table bills
--------------------
id   city_id  amount
1       1      100
2       1      100
3       2      100
4       3      100
5       4      100
6       3      100
7       1      100

Here is the query

SELECT c.city_name, SUM(b.amount) as total
FROM bills as b INNER JOIN cities as c
    ON c.id = b.city_id
GROUP BY c.id
ORDER BY total DESC;
Sébastien Maloron
  • 441
  • 2
  • 6