MySql get records or data by daily, weekly, monthly and yearly

by Posted on

In this example i am going show you some MySql helpful queries to display any user or product analytic reports about your products performance.

Suppose you have a product base website and you have to calculate the number of clicks and unique clicks on product, Or you have to display a graph view of monthly or yearly clicks or unique clicks, For that purpose these queries are very useful.




Here i have a product analytic table with some analytic data.

product_analytic

Product_Id Click IP Created Updated
1 1 192.168.1.1 2016-01-01 00:00:00 2016-01-01 00:00:00
1 1 192.168.1.1 2016-01-01 00:00:00 2016-01-01 00:00:00
2 1 192.168.2.1 2016-01-02 00:00:00 2016-01-02 00:00:00
.. .. 1.. … …
.. .. 1.. … …
.. .. 1.. … …

From this table i have to fetch clicks and uniques clicks on product day, week, month, year wise.

Fetching data by daily

    SELECT
        DATE(created) AS date,
        COUNT(click) AS click,
        COUNT(DISTINCT(ip)) AS unique_click
    FROM  product_analytic 
    WHERE created BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
    GROUP BY date
    ORDER BY date

OUTPUT:

uclick

Fetching data by weekly

    SELECT
        DATE_FORMAT(created, '%X-%V') AS date,
        COUNT(click) AS click,
        COUNT(DISTINCT(ip)) AS unique_click
    FROM  product_analytic 
    WHERE created BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
    GROUP BY date
    ORDER BY date



Fetching data by monthly

    SELECT
        DATE_FORMAT(created, '%Y-%m') AS date,
        COUNT(click) AS click,
        COUNT(DISTINCT(ip)) AS unique_click
    FROM  product_analytic 
    WHERE created BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
    GROUP BY date
    ORDER BY date

Fetching data by yearly

    SELECT
        DATE_FORMAT(created, '%Y') AS date,
        COUNT(click) AS click,
        COUNT(DISTINCT(ip)) AS unique_click
    FROM  product_analytic 
    WHERE created BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
    GROUP BY date
    ORDER BY date

If you like this post please don’t forget to subscribe my public notebook for more useful stuff

Published by admin