mysql counting rows with loop

Question

I have the following table:

+-------------+--------------+ 
| product     | purchased    |
+-------------+--------------+ 
| Hammer      | <timestamp>  |
| Nipper      | <timestamp>  |
| Saw         | <timestamp>  |
| Nipper      | <timestamp>  |
| Hammer      | <timestamp>  |
| Hammer      | <timestamp>  |
| Saw         | <timestamp>  |
| Saw         | <timestamp>  |
| Saw         | <timestamp>  |
+-------------+--------------+

I would like to see a summary about the warranty status of these products. The Warranty should be 5 years from the purchase date. So with the date of today it should be possible to check if a product is under warranty or not (base on the purchase date). Also I would like to now when the last bought product will be out of warranty. Here is a example table:

+-------------+--------------+----------------+------------------+------------------+
| product     | count        | warranty valid | warranty expired | last p. warranty |
+-------------+--------------+----------------+------------------+------------------+
| Hammer      | 3            | 1              | 2                | 10.03.2015       |
| Nipper      | 2            | 2              | -                | 01.01.2014       |
| Saw         | 4            | 1              | 3                | 02.12.2013       |
+-------------+--------------+----------------+------------------+------------------+

I tried to create the query for this example, but I didn't know how to loop the results for every product to count valid warrantys and so on. That's what I have at the moment:

SELECT
    product,
    date_format(from_unixtime(purchased), '%d.%m.%Y') AS purchaseDate,
    date_format(date_add(from_unixtime(purchased), INTERVAL 5 YEAR), '%d.%m.%Y') AS warrantyEnd,
    (
        SELECT
            COUNT(product)
        FROM
            productWarranty
    ) AS count
FROM
    productWarranty s
GROUP BY
    product

I don't know how to write the subquery because, I need to use something like WHERE product = Hammer. But how can I tell sql to loop for every single product?

JDB · Accepted Answer · 2013-10-21 16:59:59Z

What you need to work with is whether the warranty is still valid or expired and the warranty date, so you first need to build a table that reflects that:

select   product
       , IF( warranty >= NOW(), 1, 0 ) as valid
       , IF( warranty < NOW(), 1, 0 ) as expired
       , warranty as last
from     (
           select   product
                ,   ADDDATE( purchased, INTERVAL 5 YEAR ) as warranty
           from     productWarranty
         ) w
group by product
;

That would get you something like that:

+---------+-------+---------+---------------------+
| product | valid | expired | warranty            |
+---------+-------+---------+---------------------+
| Hammer  |     1 |       0 | 2017-01-01 00:00:00 |
| Nipper  |     1 |       0 | 2017-01-01 00:00:00 |
| Nipper  |     1 |       0 | 2017-01-01 00:00:00 |
| Nipper  |     1 |       0 | 2017-01-01 00:00:00 |
| Saw     |     1 |       0 | 2017-01-01 00:00:00 |
| Saw     |     0 |       1 | 2011-01-01 00:00:00 |
| Saw     |     1 |       0 | 2017-01-01 00:00:00 |
| Saw     |     1 |       0 | 2017-01-01 00:00:00 |
+---------+-------+---------+---------------------+

Then use aggregate functions to filter and sum up the information you're looking for:

select   product
       , SUM( IF( warranty >= NOW(), 1, 0 ) ) as valid
       , SUM( IF( warranty < NOW(), 1, 0 ) ) as expired
       , MAX( warranty ) as last
from     (
           select   product
                  , adddate( purchased, interval 5 year ) as warranty
           from productWarranty
         ) w
group by product
;

+---------+-------+---------+---------------------+
| product | valid | expired | last                |
+---------+-------+---------+---------------------+
| Hammer  |     1 |       0 | 2017-01-01 00:00:00 |
| Nipper  |     3 |       0 | 2017-01-01 00:00:00 |
| Saw     |     3 |       1 | 2017-01-01 00:00:00 |
+---------+-------+---------+---------------------+

JDB · Accepted Answer · 2013-10-21 15:07:06Z

2

Better normalization would speed up this query tremendously, but here's an example to work from:

First, you need to start with your base query, the one that will drive the results. In this case, it's a list of all the products in your table:

SELECT DISTINCT product
FROM   productWarranty

The above query creates a sort of "temp" table that we can base our queries on. If you have a separate, normalized product table then that would be much better.

The rest of the columns can simply be subqueries driven by your base table:

Total count:

SELECT COUNT(*)
FROM   productWarranty
WHERE  product = 'Hammer'

Total valid warranties:

SELECT COUNT(*)
FROM   productWarranty
WHERE  product = 'Hammer'
   AND purchased >= <warranty cut-off date>

Total expired warranties:

SELECT COUNT(*)
FROM   productWarranty
WHERE  product = 'Hammer'
   AND purchased < <warranty cut-off date>

Last purchase date:

SELECT MAX( purchased )
FROM   productWarranty
WHERE  product = 'Hammer'

Now to combine all of these into a single query:

SELECT  Base.product
    ,   (
            SELECT COUNT(*)
            FROM   productWarranty
            WHERE  product = Base.product
        ) AS TotalCount
    ,   (
            SELECT COUNT(*)
            FROM   productWarranty
            WHERE  product = Base.product
               AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()
        ) AS ValidWarrantyCount
    ,   (    
            SELECT COUNT(*)
            FROM   productWarranty
            WHERE  product = Base.product
               AND date_add(from_unixtime(purchased), INTERVAL 5 YEAR) < CURDATE()
        ) AS ExpiredWarrantyCount
    ,   (
            SELECT MAX( purchased )
            FROM   productWarranty
            WHERE  product = Base.product
        ) AS LastPurchased
FROM    (
            SELECT DISTINCT product
            FROM   productWarranty
        ) AS Base

edited Oct 21, 2013 at 15:07

answered Oct 21, 2013 at 14:56

JDB

26.1k5 gold badges81 silver badges132 bronze badges

3 Comments

emjay Over a year ago

Hi, at first thanks for your solution. It works great. I've tried to add a JOIN commant, because I would like to get the company name added to this table. But it isn't working. :-/ I tried to add it to the base query without success. Any ideas?

JDB Over a year ago

@emjay - I think you can find plenty of examples on how to use a JOIN (this, for example, or this). If you are still struggling, though, please post a new question. Stack Overflow is not a message board.

emjay Over a year ago

Okay, I made a SQL Fiddle, so i really should make a new Question? because it's just about extending your query with a join. I tried it, but it doesn't work. I don't know where to place it, i thought in the base query, but that doesn't work. (sqlfiddle.com/#!2/60396/1)

Community · Accepted Answer · 2017-05-23 10:31:46Z

0

Here's the answer I posted in your other question

SELECT  p2c.pid AS productNumber,
        p.name AS productName,
        COUNT(*) AS registered,
        SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) >= CURDATE()) AS inWarranty,
        SUM(date_add(from_unixtime(purchased), INTERVAL 5 YEAR) < CURDATE()) AS outOfWarranty,
        DATE_FORMAT( MAX( from_unixtime(purchased) ), '%d.%m.%Y') AS lastPurchased,
        DATE_FORMAT( date_add( MAX( from_unixtime(purchased) ), INTERVAL 5 YEAR), '%d.%m.%Y') AS warrantyUntil
FROM products2customers p2c
JOIN products p ON p.id = p2c.pid
GROUP BY p2c.pid
ORDER BY inWarranty DESC

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Oct 25, 2013 at 8:24

Barmar

789k57 gold badges554 silver badges669 bronze badges

2 Comments

emjay Over a year ago

Thanks again Mr. Barmar. :) Hope to see you in future questions. ;-) Have a nice Day.

JDB Over a year ago

I know that the OP asked you to post this here, but your answer does not answer the stated question (there's no mention of joins). Renaud's is essentially the same as yours but more appropriate to this particular question.

Collectives™ on Stack Overflow

mysql counting rows with loop

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related