How to collect SQL data group by month and sort by month-CodePudding

Here is the PHP code I am using to count and group last 30 days users from different countries.

$result = $dbh->query('SELECT country,COUNT(*) FROM data_able WHERE dtime > DATE_SUB(CURDATE(), INTERVAL 31 DAY) GROUP BY country ORDER BY COUNT(country) DESC;');
$i = 1;
foreach ($result as $row) {
    ${'country' . $i} = $row['country'];
    ${'count' . $i} = $row['COUNT(*)'];
    $i  ;
}
echo $count1;

The $count1; will output the count of a country which is counted most in this table. I would like to use the group by feature to get data by months (April, March - not 04/03). I tried many ways, but none of them seems to be working.

Here is the Database example db<>fiddle

in short instead of collecting data group by country, I would like to collect data group by months (total count per month in table) of last 12 months. and use them as $month1; $month2; etc..

as example

$month1 should output April (this month)

$month2 should output march (previous month)

$monthcount1 should output 1 (as only 1 tdate entry)

$monthcount2 should output 2 (as only 2 tdate entry)

what should be the SQL query ?

CodePudding user response：

If you just want the 12 months, you can do it with case statements like:

SELECT country, 
       SUM(CASE WHEN MONTH(dtime) = 1 THEN 1 ELSE 0 END) JAN_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 2 THEN 1 ELSE 0 END) FEB_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 3 THEN 1 ELSE 0 END) MAR_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 4 THEN 1 ELSE 0 END) APR_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 5 THEN 1 ELSE 0 END) MAY_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 6 THEN 1 ELSE 0 END) JUN_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 7 THEN 1 ELSE 0 END) JUL_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 8 THEN 1 ELSE 0 END) AUG_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 9 THEN 1 ELSE 0 END) SEP_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 10 THEN 1 ELSE 0 END) OCT_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 11 THEN 1 ELSE 0 END) NOV_TOTAL,
       SUM(CASE WHEN MONTH(dtime) = 12 THEN 1 ELSE 0 END) DEC_TOTAL,
       count(*) TOTAL
  FROM data_able 
 WHERE 
 GROUP BY country 
 ORDER BY COUNT(*) DESC

I'm not sure what platform you're using and the datatype of dtime, so I didn't fill in the WHERE clause to filter to the last year. I think you're using SQL server or equivalent since you have the CURRDATE() function, so I think it can just be CURRDATE()-365, but I'm not sure.

CodePudding user response：

If you want to display a specific row of data you can use a Ranking function to get the specific month from data

or You can use the Elias name , but I do not recommend it

CodePudding user response：

It seems to me that you are asking multiple and different questions in the same time. So I would say: Applying the received data to your variables in php and further processing them is your part. What I can answer is how to get the count of dates per month. Assuming your goal is to get such a result...

April 1, March 2, February 2, January 2, December 4

...you can use a construct of DATENAME and COUNT with the condition "within the last 12 months" ordered by the latest month as first:

SELECT DATENAME(MONTH,tdate) monthName, COUNT(tdate) counter
FROM data 
WHERE tdate >= DATEADD(Month, -12, GETDATE())
GROUP BY DATENAME(MONTH,tdate),(tdate)
ORDER BY tdate DESC;

P.S.: The DB example you've linked is a MS SQL DB, therefore I wrote my answer for MS SQL, too. But you tagged MY SQL as DB you're using. You should take care about this.