Page 1 of 1
stats from 2 tables in one query
Posted: Tue Oct 03, 2006 3:05 am
by rsmarsha
Is it possible to say gather count values from 2 columns in 2 different tables, using the 1 query? I currently have 2 queries to count up the totals in 2 columns which are then added together. There isn't anything linking at present so i can't use a join i don't think.
The first table is the old orders table and the 2nd is the new one.
Posted: Tue Oct 03, 2006 3:11 am
by twigletmac
Possible to have a bit more info about the table structures and the data you're trying to output? (Oh and what database server and version are we talking about?)
Mac
Posted: Tue Oct 03, 2006 3:50 am
by rsmarsha
They are 2 orders tables. One is old with all order and product info in the one table, the other a new table with just order info, products are in another table.
I need to count orders from both tables, so say daily orders, weekly and so on. For stats such as month and year i have to have both tables counted and added together. This means 2 queries. It's not really a big problem as i am setting it up to run a cron every 12 hours and input into a stats table. I was just wondering if there is a way to do it in 1.
Db is MySQL 4.0.25.
Posted: Tue Oct 03, 2006 6:44 am
by Zoxive
Something like..
Code: Select all
Select COUNT(cats.Total) + COUNT(dogs.Total) as `colorBlack_Total` From cats,dogs WHERE cats.color = 'Black' AND dogs.color = 'Black'
-NSF
Posted: Tue Oct 03, 2006 6:49 am
by onion2k
rsmarsha wrote:They are 2 orders tables. One is old with all order and product info in the one table, the other a new table with just order info, products are in another table.
I need to count orders from both tables, so say daily orders, weekly and so on. For stats such as month and year i have to have both tables counted and added together. This means 2 queries. It's not really a big problem as i am setting it up to run a cron every 12 hours and input into a stats table. I was just wondering if there is a way to do it in 1.
Db is MySQL 4.0.25.
Presumably, as one is a table of old orders, no new data is getting inserted into it? Can't you just query the new orders and add a number on for the number in the old table?