SQL query with math

Hello,

I was wondering if I could build a query with math? I am way out of my league here but I am trying to find the best way to create a report.

Sample Data

Day one
----------------------
product_id|qty|status|log_time
5|15|1|2015-07-17 07:00:00
10|5|1|2015-07-17 07:00:00

Day Two
----------------------
product_id|qty|status|log_time
5|20|1|2015-08-17 07:00:00
10|4|1|2015-08-17 07:00:00
11|0|0|2015-08-17 07:00:00
14|2|1|2015-08-17 07:00:00

Day Three
----------------------
product_id|qty|status|log_time
5|0|0|2015-09-17 07:00:00
10|4|1|2015-09-17 07:00:00
11|2|1|2015-09-17 07:00:00
14|6|1|2015-09-17 07:00:00

Day Four
----------------------
product_id|qty|status|log_time
5|5|1|2015-10-17 07:00:00
10|0|0|2015-10-17 07:00:00
11|6|1|2015-10-17 07:00:00
14|8|1|2015-10-17 07:00:00
18|9|1|2015-10-17 07:00:00
16|1|1|2015-10-17 07:00:00

This is the information I am needing to retrieve:

product_id|current_qty|current_status|days_tracked|days_out_of_stock(days status=0)|days_in_stock(days status=1)|average_qty

I know that I can pull this with mysql and do the math with php but I am concerned that it will take to long, there are going to be about 200k records by the end of the year and I was thinking running a sql script to do the math, if possible.

I would love any help, thank you for your time.
LVL 1
movieprodwAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Try this please. It may need refinements because the small data sample might not represent all issues in particular I'm not sure if counting the number of distinct log_time values will be accurate as number of days.
SELECT
      t.`product_id`
    , AG.current_qty
    , AG.current_status
    , COUNT(DISTINCT T.`log_time`) as days_tracked
    , SUM(CASE WHEN T.`qty` = 0 THEN 1 ELSE 0 END) AS days_out_of_stock
    , SUM(CASE WHEN T.`qty` = 1 THEN 1 ELSE 0 END) AS days_in_stock
    , avg(T.`qty`) as avg_qty
FROM YourTable T
INNER JOIN (
          SELECT
                t.`product_id`
              , `qty`    AS current_qty
              , `status` AS current_status
          FROM YourTable T
          INNER JOIN (
                  SELECT
                      `product_id`
                    , max(`log_time`) as `log_time`
                  from YourTable
                  GROUP BY
                    `product_id`
                  ) MX ON T.`product_id` = MX.`product_id` 
                       AND T.`log_time` = MX.`log_time`
        ) AG ON T.`product_id` = AG.`product_id`  
GROUP BY
      t.`product_id`
;

Open in new window

From the sample provided I go this result by that query:
| product_id | current_qty | current_status | days_tracked | days_out_of_stock | days_in_stock | avg_qty |
|------------|-------------|----------------|--------------|-------------------|---------------|---------|
|          5 |           5 |              1 |            4 |                 1 |             0 |      10 |
|         10 |           0 |              0 |            4 |                 1 |             0 |    3.25 |
|         11 |           6 |              1 |            3 |                 1 |             0 |  2.6667 |
|         14 |           8 |              1 |            3 |                 0 |             0 |  5.3333 |
|         16 |           1 |              1 |            1 |                 0 |             1 |       1 |
|         18 |           9 |              1 |            1 |                 0 |             0 |       9 |

Open in new window

and the DDL is
CREATE TABLE YourTable
    (`product_id` int, `qty` int, `status` int, `log_time` datetime)
;
    
INSERT INTO YourTable
    (`product_id`, `qty`, `status`, `log_time`)
VALUES
    (5, 15, 1, '2015-07-17 07:00:00'),
    (10, 5, 1, '2015-07-17 07:00:00'),
    (5, 20, 1, '2015-08-17 07:00:00'),
    (10, 4, 1, '2015-08-17 07:00:00'),
    (11, 0, 0, '2015-08-17 07:00:00'),
    (14, 2, 1, '2015-08-17 07:00:00'),
    (5, 0, 0, '2015-09-17 07:00:00'),
    (10, 4, 1, '2015-09-17 07:00:00'),
    (11, 2, 1, '2015-09-17 07:00:00'),
    (14, 6, 1, '2015-09-17 07:00:00'),
    (5, 5, 1, '2015-10-17 07:00:00'),
    (10, 0, 0, '2015-10-17 07:00:00'),
    (11, 6, 1, '2015-10-17 07:00:00'),
    (14, 8, 1, '2015-10-17 07:00:00'),
    (18, 9, 1, '2015-10-17 07:00:00'),
    (16, 1, 1, '2015-10-17 07:00:00')
;

Open in new window

see this working here: http://sqlfiddle.com/#!9/da9d31/3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
movieprodwAuthor Commented:
This is amazing! Thank you so much for your help.
PortletPaulEE Topic AdvisorCommented:
no problem.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

movieprodwAuthor Commented:
I am getting an error, I can try to figure it out but maybe you know what it is right off?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS current_qty , `status` END AS current_status FROM You' at line 13
PortletPaulEE Topic AdvisorCommented:
Sorry, it doesn't mean much to me as I cannot reproduce the problem.

Note I provided a tested query (go to the sqlfiddle url),
if you made ANY CHANGES (at all) then it is likely to be as a result of those changes.
That is not a criticism, it's just the way it works.
movieprodwAuthor Commented:
Please note that I was clicking your fiddle link, but I realized you just posted an older one, I changed the 2 to a 3 in the link and it works great!
PortletPaulEE Topic AdvisorCommented:
oh, sorry. didn't realize you were referring to sqlfiddle
PortletPaulEE Topic AdvisorCommented:
fixed that URL (may need to refresh)
movieprodwAuthor Commented:
I am able to follow what you did here, very cool. The only thing I can see is that it is not counting the number to total days in stock.
movieprodwAuthor Commented:
Nevermind, I see that you had qty but it should have been status, for days in stock.

Thank you again, my brain is happy reading this code :p
PortletPaulEE Topic AdvisorCommented:
oh dear, that is what I had intended. In my defense I did say it might need tweaking :0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.