I find myself in need of creating a specific report without the foggiest idea where to begin. I could pull the data as an external datasource in Excel or run a query directly at the MySql command prompt, but I'm not sure how to structure the query.

The data will be coming from two tables. Here are the relevant fields:

A table called ORDERS has fields ORDERID and DATE. In this table, ORDERID is a primary key.
A table called ORDER_DETAILS has fields ORDERID, PRODUCTCODE, and QUANTITY. ORDERID is not unique in this table: Each line item for a given order has it's own row and each rown within an order has the same ORDERID.

As far as table joins go, ORDERS.ORDERID=ORDER_DETAILS.ORDERID.

ORDERS.DATE is a UNIX-style timestamp.

I need to generate a report that shows the number of each product sold on each day. For instance, if my PRODUCTCODEs are "WIDGET1" thru "WIDGET5" and I'm running the report for the first week of this year, then the output should look something like this:


To make it slightly more complicated, it would be especially excellent to exclude dates on which any of the numbers are zero. (We're trying to figure out the percentage of sales generated by each product during times that no product is on backorder and therefore unavailable for sale. In this way, we'll be able to forecast sales and calculate proper order amounts for future orders.) Additional wrinkle: Since we also have discontinued products, I'm hoping to also make it also exclude dates on which WIDGET6 through WIDGETn have quantity sums greater than 0. Ugh. (Our product codes aren't so nicely organized, either - each one needs to be specified individually since some are purely numeric and others are alphanumeric and they all come in no particular order. I blame my predecessor who had much to learn about data normalization.)

I know that the numbers will take some form of SUM(ORDER_DETAILS.QUANTITY) grouped by "PRODUCTCODE,DATE", but my experience with subqueries is limited at best and it seems like this is going to require some sort of nested subqueries.

To make things even more difficult, we're still running MySQL 4.1, which has more limited subquery capabilities than MySQL 5, and so this may not even be possible. If absolutely necessary, I can import all the data into a local system running MySQL 5 on a testbed, but I'd rather avoid that step if possible since I'd like this to be a report that we can generate on the fly in the future. (In fact, if I can get the query working in MySQL 4, then I'll be creating a web-based interface for it so that my users can run it whenever they feel like it for whatever date range they need. If it can only happen after exporting the data to another system, then that won't be possible which means I'll have do the export and run the report for them every time they feel like they need the data. Yuck.)

It MAY be far easier to do this as a data import in Excel and generate the table from there, but unfortunately that is another thing that I have only limited experience with and I would still need to figure out how to construct the query to group everything by date (from a UNIX timestamp, no less) and sum up the quantities for each productcode.

Any help would be GREATLY appreciated.

Thank you,
Wayne

Don't know if this will work in MySql 4, but it should:


You'll need to figure out how your database will format or truncate the dates for you.

Hope this helps.

Wow. That's exceptionally cool. I was unaware of the CASE statement in SQL. Looking in the manuals, it appears that it was introduced to MySql in version 5, but this solution looks very good to me and I doubt that it's possible to emulate in version 4. So I'll run it on exported data for now and use this as one more argument for the need to upgrade.

Thank you VERY much!

Nope, "CASE was added in MySQL 3.23.3" - 11.3. Control Flow Functions

If you don't want CASE, you can use IF() same way. Good luck.