php - get monthwise row count from datewise data in mysql

973

I have two tables:

 table 1.a
    id--entry_date-amount
============================
    2---2016-04-14--$400
    3---2016-04-14--$400
    4----2017-07-14--$200
    5---2017-07-14--$500
    6---2017-05-14--$600
    7----2017-06-18--$100

    table 2.b
    id--entry_date
===========================
    2---2016-04-14--$230
    3---2016-04-14--$230
    4----2017-07-14--$567
    5---2017-07-14--$600
    6---2017-05-14--$560
    7----2017-06-18--$90
    8---2016-04-14--$100

from the two tables how can i get count with montwise my desired result:

month_name--total(count form table a)--total(count form table b)--amount(table a)--amount(table b)
========================================================
April,16

this is the demo data. I want to show data from two tables in a single query month wise. How can i do this?

I tried:

{-code-3}

But takes 58 seconds for simple query. How can i make this in a simple query?

58

Answer

-2
876

Answer

---------3---$800-$500 May,17
56

Answer

-1
386

Answer

---------1 --$600--$700 June,17
361

Answer

-2
787

Answer

---------2--$100--$800 July,17
785

Answer

-2
759

Answer

---------2---$700-$400|||SELECT MONTHNAME(r.entry_date),r.a_total FROM( SELECT IFNULL((SELECT COUNT(tr.id) AS amount FROM a AS tr WHERE MONTH(tr.entry_date)=MONTH(t.entry_date)),0) AS a_total ,t.entry_date FROM(SELECT tr.id,tr.entry_date FROM a AS tr WHERE DATE(tr.entry_date) BETWEEN '2017-07-01' AND '2018-06-30') t GROUP BY MONTH(t.entry_date)) r
8

Answer

Solution:

You can get the counts and sum from each table individually, then useUNION to combine the two result sets into one result set. Something like this :

SELECT Month_name,
  SUM(aCount) AS aCount,
  SUM(bCount) AS bCount,
  SUM(aAmount) AS aAmount,
  SUM(bAmount) AS bAmount
FROM
(
  SELECT  
    MONTHNAME(a.entry_date) AS Month_name,
    COUNT(a.id) AS aCount,
    0 AS bCount,
    SUM(a.amount) AS aAmount,
    0 AS bAmount
  FROM a
  GROUP BY MONTHNAME(a.entry_date)
  UNION ALL
  SELECT  
    MONTHNAME(b.entry_date) AS Month_name,
    0 AS aCount,
    COUNT(b.id) AS bCount,
    0 AS aAmount,
    SUM(b.amount) AS bAmount
  FROM b
  GROUP BY MONTHNAME(b.entry_date)
) AS t
GROUP BY Month_Name;

277

Answer

Solution:

user9131497 has a good design for the big picture. However, I would suggest stuff like this for handling the dates:

SELECT  DATE_FORMAT(entry_date, "%M,%y") AS 'Month',
        COUNT(*) AS 'aCount'
    FROM a
    GROUP BY LEFT(entry_date, 7)  -- eg, "2017-03"

Try that to see what I mean.

Note that this will work beyond a year. Or did you need January values from all years to be combined?? -- That's what your solution and user9131497's will do. Mine keeps them separate.

People are also looking for solutions to the problem: php - select all products from child categories in parent category

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.