7

I have a MYSQL database that contains data on my companies customers as they use our services each day.

each table in the database represents one 24 hour period and all customer transactions that took place in that period.

I would like to count the total transactions over a number of days

so far I have a select statement that does the following 

select 
  (
    select count(customer) from 2010Dec28 
    where customer='<customer name>'
  ) as t1,
  (
    select count(customer) from 2010Dec29 
    where customer='<customer name>'
  )as t2;`

but this returns the result as two separate counts

| t1   | t2    |
| 1438 | 16282 |
1 row n set (0.00 sec)`

My question is how can I generate the sum of these two results without having to do so in my application code.

Improve this question
4
  • 4
    I think "each table in the database represents one 24 hour period" is the point where you should stop and reconsider your design. Commented Jan 23, 2011 at 22:46
  • 1
    Follow the advice people are giving you and fix your design. You should not have a separate table for each date. At most you might have one table for live data and one table for archived data. The date is a column in various rows in a table, not the name of a table. Then querying for counts by date is straightforward as can be. If you need an interim solution until you can fix your design, use a UNION ALL query to combine multiple queries into one, then wrap the whole union into another SELECT to SUM all the counts. Commented Jan 23, 2011 at 22:49
  • each table for each day is a worst idea ever i seen, inefficient, unmaintainable Commented Jan 23, 2011 at 22:50
  • 1
    wow ok definitely going to be redesigning the way I do this, In my defence I'm a network engineer by trade and have been knocking this together in my spare time to get some stats from various log files Commented Jan 23, 2011 at 22:59

3 Answers 3

Reset to default
12

Tyler is probably right, design is surely wrong!

But it is possible..

select 
    sum(a.count)
from
    (select count(*) as count from table1
     union all
     select count(*) as count from table2) a
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the suggestion but this gives me an error like ERROR 1248 (42000): Every derived table must have its own alias
ok, that varies by rdbms and im not that familar with mysql, i've edited to add an alias.
Ok this works for mysql, thanks for the help, this will be used in the short term whilst I design the db again.
10

Fix your database design. Unless you're doing some sort of mass data warehousing on billions of rows, a seperate table per day is very improper.

Improve this answer

Comments

5

Just swap the cross join for a direct sub + sub, per below. Much easier than making any unions or joins

select 
  (
    select count(customer) from 2010Dec28 
    where customer='<customer name>'
  ) + (
    select count(customer) from 2010Dec29 
    where customer='<customer name>'
  )
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.