Populate data as individual days of the months as columns

Question

I have created a query that gives me a sum of sales for each department for the day.

What I want is to generate this data for the whole month using one master query.

This is the result I expect to see:

Department No  Department Name  1-April  2-April  3-April
1               Infants          100      112      96
2               Kids             120      132      123
Total Total                      220      144      219

Currently, I can generate this for one day only, and I have to keep changing the date value to get the data for the next day.

As stated earlier, I want to run a this for the whole month in one master query.

To improve the quality of your Question, please show us the code you have been using as well as the fields of your table(s), with sample data. Also, please show us the code that you have developed so far. Finally, if you can include a script to create and populate those tables, then that will enable us to readily reproduce your situation. — toonice, Apr 19 '17 at 08:19
This is the code I use to generate daily sales SELECT Department Name, Department No, sum(Total Sales)*-1 as Net_Amount FROM Item Master where Date = '2017-04-01' GROUP BY Department Name, Department No — Sequel3, Apr 19 '17 at 08:59

score 0 · Answer 1 · edited May 23 '17 at 12:02

Please try the following...

SELECT CONCAT( 'SELECT department.id AS `Department No`,
                       department.departmentName AS `Department Name`',
               GROUP_CONCAT( ', SUM( date_',
                             DATE_FORMAT( salesDate, '%d_%M' ),
                             '.sales ) AS `',
                             DATE_FORMAT( salesDate, '%e-%M' ),
                             '`',
                             SEPARATOR '' ),
               ' FROM department',
               GROUP_CONCAT( ' LEFT JOIN departmentSales AS date_',
                             DATE_FORMAT( salesDate, '%d_%M' ),
                             ' ON department.id = date_',
                             DATE_FORMAT( salesDate, '%d_%M' ),
                             '.department_id AND date_',
                             DATE_FORMAT( salesDate, '%d_%M' ),
                             '.salesDate = `',
                             DATE_FORMAT( salesDate, '%Y-%M-%d' ),
                             '`',
                             SEPARATOR '' ),
               ' GROUP BY department.id
                ORDER BY department.id
                UNION
                SELECT `Totals`,
                       ` `,
               GROUP_CONCAT( ', SUM( date_',
                             DATE_FORMAT( salesDate, '%d_%M' ),
                             '.sales )',
                             SEPARATOR '' ),
               ' FROM department',
               GROUP_CONCAT( ' LEFT JOIN departmentSales AS date_',
                             DATE_FORMAT( salesDate, '%d_%M' ),
                             ' ON department.id = date_',
                             DATE_FORMAT( salesDate, '%d_%M' ),
                             '.department_id AND date_',
                             DATE_FORMAT( salesDate, '%d_%M' ),
                             '.salesDate = `',
                             DATE_FORMAT( salesDate, '%Y-%M-%d' ),
                             '`',
                             SEPARATOR '' ),
               ' ) INTO @salesQuery
FROM ( SELECT DISTINCT salesDate
       FROM departmentSales
       WHERE salesDate BETWEEN FROM_DAYS( TO_DAYS( targetDate ) -
                                          DAYOFMONTH( targetDate ) +
                                          1
                                        )
                           AND LAST_DAY( targetDate )
     ) AS salesDateFinder;

PREPARE salesTotals FROM @salesQuery;
EXECUTE salesTotals;

This attempt is based on code found at mysql select dynamic row values as column names, another column as value. I am concerned about the group_concat_max_len variable being a problem. If it is then I'll step up my research into the CASE method eggyal mentioned. If it is not a problem, then I'll take things a bit easier.

Most of the lines here actually form just one SQL statement. It takes the list of salesDate values constructed by the subquery near the end, which are then used to help construct a rather long string containing the SQL statement to be run against the database.

The string is formed from a list of substrings specified as the arguments to the function CONCAT(), which you can read about at https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat.

The string starts by specifying that the fields id and departmentName from the table department should be the first selected for each record, followed by the SUM() of each date's sales for that department. Each date field is given a name like date_09_March and in the output a heading like 9-March.

The repeated segments of the string are achieved by using the GROUP_CONCAT function, which you can read about at https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat.

After the fields are specified the string specifies that the values should come from a temporary table formed from department LEFT JOINed with an instance of the departmentSales table for each date, with each instance being given an alias like date_09_March and joined on its department_id field to the id field of department.

Once it is formed, the temporary table's records are grouped by department.id so that the totals for each department may be calculated. The output is then sorted by department.id.

The statement used to produce this sorted output and its column headings is then joined to another (somewhat simpler) statement used to generate the totals row. The string containing the SQL statement formed by the concatenation of each substring is then assigned to the variable @salesQuery, prepared and executed.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Further Reading

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html (for information on the DATE functions used)

Explanation added. Also, code revised to include a `WHERE` clause that limits the dates chosen to just those for the month of the date referred to here as `targetDate`. `targetDate` can be any date within the month. — toonice, Apr 19 '17 at 12:16

score 0 · Answer 2 · answered Apr 20 '17 at 06:24

0

Thanks for all the answers I managed to write a query based on the Pivot Function.

answered Apr 20 '17 at 06:24

Sequel3

75
1
1
9

score -2 · Answer 3 · answered Apr 19 '17 at 08:29

-2

You can control the range of dates with something like this:

select (...) where v_date >= 1-April and v_date <= 30-April

(consider v_date the name of the column and 1-April in date format)

answered Apr 19 '17 at 08:29

rtrigo

519
1
6
16

That wouldnt get what I want Rui – Sequel3 Apr 19 '17 at 09:01
That pseudocode would only get the specified fields for records that fall within the specified period. `Sequel3` is asking for the date values to become fields themselves, with the sum of Sales for that date and that department filling the fields so created. – toonice Apr 19 '17 at 10:14
Alright, thanks for the explanation. Guess I got it wrong. – rtrigo Apr 19 '17 at 10:31
Hi Toonice, I am getting errors with your code.To simplify this,can you declare what texts are variables?(like for instance I get an error on 'DATE_FORMAT' - 'DATE_FORMAT' is not a recognized built-in function name.) So I can change the values only for the text that needs to be changed(DepartmentID,Department.Departmentname is what I should change) – Sequel3 Apr 19 '17 at 11:33

Populate data as individual days of the months as columns

3 Answers3