Question:
There is a certain table with dates and other infoy. it is necessary to group information only by months. Here is what I tried
Select наименование, month(дата)
FROM таблица1
Group by month(дата), наименование
This code groups, but only if the date exactly matches. It is necessary for me that the grouping occurs only by months, that is, there should be only 12 records. What did I do wrong?
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, either COUNT
or SUM
, and the above code will work fine. If it works only if the date completely matches, then you are not correctly selecting the month. I don’t remember the subtleties of MS-Access, but in PostgreSQL it will 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 Наименование
, then naturally there will be not 12 entries, but will depend on the number of different items sold in these months. Although, if from Delphi, then it will be even more convenient, there you will group by months in the plate and it will be a plus sign to beautifully unfold every month with the amount in the footer