Question:
There is a certain table with dates and other info. it is necessary to group the info only by months. Here is what I tried
Select наименование, month(дата)
FROM таблица1
Group by month(дата), наименование
This code groups, but only if the date is exactly the same. I need the grouping to occur only by months, that is, there should be only 12 records. What was my mistake?
Answer:
I was not mistaken in anything, if you need an analysis of sales by months, then there will be one of the aggregate functions or COUNT or SUM and the above code will work fine. If it only works if the date is exactly the same, then you are not selecting the month correctly. I don’t remember the intricacies of MS-Access, but in PostgreSQL it would look like this:
SELECT month, COUNT(*) FROM art --Или SUM(fieldname)
JOIN LATERAL EXTRACT(MONTH FROM дата) month ON TRUE --Просто чтобы не писать то же самое в GROUP BY
WHERE дата IS NOT NULL
GROUP BY month
ORDER BY month
Well, if, after all, the Наименование , then naturally there will be not 12 records, but will depend on the number of different names sold in the given month. Although, if from Delphi, then it will be even more convenient, there in the table you will do the grouping by months and it will be a plus sign to unfold beautifully every month with the amount in footer