0

I just wanted to add different columns from different tables... Has anyone any idea on how to do that?

Consider I have 3 tables as below

  1. tv sales
  2. AC sales
  3. cooler sales

And the tables data as follows

1)Tv Sales

 Id Date NoOfSales Totalamount 1 03/05/2014 10 10000 2 04/05/2014 20 20000 3 05/05/2014 30 30000

2)Ac Sales

 Id Date NoOfSales Totalamount 1 03/05/2014 10 50000 2 04/05/2014 20 60000 3 05/05/2014 30 70000

3)cooler Sales

 Id Date NoOfSales Totalamount 1 03/05/2014 10 30000 2 04/05/2014 20 60000 3 05/05/2014 30 70000

Now I want to add the "Totalamount" from all the tables for a particular "date" for example I need totalamount on 03/05/2014 as 90000

Improve this question

2 Answers 2

Reset to default
1

In MySQL, the easiest way to do this is with union all and aggregation:

select date, sum(totalamount) as TotalSales from ((select date, totalamount from TvSales ) union all (select date, totalamount from AcSales ) union all (select date, totalamount from CoolerSales ) ) t group by date;

The reason you want to use union all is in case the dates are different in the various tables. A join makes it possible to lose rows.

Second, having three tables with the same format is an indication of poor database design. You should really have one table with the sales and a column indicating which type of product it refers to.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for the reply...but how about where condition for date ...I want total sales for particular date @Gordon Linoff
@user3531533 . . . Then use where date = XXX rather than group by date.
1

You could solve your problem by making a union of the information you want to aggregate on the different tables and them sum the amounts. This would look like:

 SELECT t.Date,SUM(t.Totalamount) FROM ( SELECT Date,Totalamount FROM tvSales UNION ALL SELECT Date,Totalamount FROM acSales UNION ALL SELECT Date,Totalamount FROM coolerSales ) t WHERE t.Date='03/05/2014' GROUP BY t.Date

It is important that the fields of the union have the same name and type. In case they haven't the same name you should create common aliases for the 2 columns across the 3 select queries and then work with these aliases on the main query. Also the UNION should be performed including the ALL keyword in order to avoid eliminating duplicate records across the three tables.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.