stats from 2 tables in one query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

stats from 2 tables in one query

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post 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.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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?
Post Reply