Database Daily Totals

[JoshMan]

Hey,

I am creating a database that keeps track of shipment weights. They are time stamped and archived for one month.

How would I go about making another database table, or a function that can find the totals per day for a monthly graph of shipments?

The database stores many shipments per day.

Thanks

[Colby.Clegg]

Well, lucky for you this is what databases are born to do. The SQL language (which is generally uniform but differs a bit from vendor to vendor) has what are called Aggregate Functions, such as MAX, MIN, and most relevant to you, SUM.

If I did a query like this…

SELECT SUM(weight) FROM weight_record

I’d get back the sum of the weights for all time.

But, you want to break it up by day. That’s what SQL’s “GROUP BY” clause is for. It lets you break up the datasets that aggregate functions apply to.

For the sake of simplicity, imagine we had a column in that weight table called “day”, which had the day of the month for that record in it. We could do the following…

SELECT day, SUM(weight) FROM weight_record GROUP BY day

and end up with the sum for each day. Trouble is, you probably don’t have that column. Instead, each row has a timestamp. So, what you need to do is find a way to use just the date portion of the whole time.

How you do that will depend on the database system you’re using, since you’ll be using a function provided by the db system instead of the sql language. Let’s assume you’re using MySQL. Going over to the MySQL Date/Time Function Reference Page we find there’s a function called Date that will extract just the date from a timestamp.

So, using that, we end up with:

SELECT DATE(t_stamp), SUM(weight) FROM weight_record GROUP BY DATE(t_stamp)

and VOILA, you end up with the weights by day. Clean it up a bit (mainly sort the data), and it’s ready to go in the graph:

SELECT DATE(t_stamp) as 'dt', SUM(weight) FROM weight_record GROUP BY dt ORDER BY dt ASC

(Note: I also gave the column an ALIAS to make things a bit easier to read and work with).

Hope that helps. Basically, I wanted to show you how to do it, but also give you some vocab as a starting point for looking up more about SQL on your own. Main points: aggregate functions, group by clause, and the order by clause.

Regards,

[JoshMan]

Thank You very much!

I’ll give it a shot.

[JoshMan]

Related Question,

What would go under the WHERE if you wanted to only look between certain dates?

WHERE dt>= Startdate AND dt<= EndDate?

We are using FSQL

[Colby.Clegg]

Yes, you could do something like that, though you could also just use the t_stamp column. Irregardless of what columns you’re retrieving, you can use any available columns in your where clause.

Why would you choose one over the other? Basically the answer comes down to performance. The t_stamp column is indexed, meaning the db can (theoretically) locate a particular value in less time than it would take to look through the whole table. If you used ‘dt’ in your where clause, the db would first need to calculate what that means for each row, and then narrow it down to what you wanted. By using timestamp, it can quickly just grab the matching rows, and then only calculate dt for those.

Ultimately in your case I’d doubt you’d notice the difference, so I’d go with what’s most readable… but I just wanted to give you some info that might come in handy later.

Oh, and yeah, your WHERE clause looks good, as long as ‘StartDate’ and ‘EndDate’ are properly formatted dates.

Regards,