[Solved] Sort by MONTH but display by MONTHNAME

Creating tables and queries
Post Reply
mehorter
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

[Solved] Sort by MONTH but display by MONTHNAME

Post by mehorter »

I have a query which is grouped and sorted by MONTH("Datetime").
This returns a table:

Code: Select all

Month    | Sum   | Count
 11       |  100  |  5
 10       |  101  |  6
 
However I would like the return to display MONTHNAME("Datetime").:

Code: Select all

Month    | Sum   | Count
 November |  100  |  5
 October  |  101  |  6
 
ORDER BY MONTHNAME("Datetime") sorts the months in alphabetical order but I would like the MONTHNAME sorted by the numerical sort that ORDER BY MONTH("Datetime") provides while showing the MONTHNAME.
What the best method to achieve this result, if possible?

My query:

Code: Select all

SELECT 
	MONTH("Datetime") "Month"
   ,SUM("Revenue")   "Sum"
   ,COUNT("Items")   "Count"
FROM 
	"table" 
WHERE 
	YEAR("Datetime") = 2017
GROUP BY 
	MONTH("Datetime")
ORDER BY 
	MONTH("Datetime") DESC
Last edited by mehorter on Thu Nov 09, 2017 3:25 pm, edited 1 time in total.
LibreOffice Version: 6.1.3.1 on Linux4.18.6-1-default x86_64 GNU/Linux Open Suse Tumbleweed
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort by MONTH but display by MONTHNAME

Post by Villeroy »

Code: Select all

SELECT
   MONTHNAME("Datetime") "Month"
   ,SUM("Revenue")   "Sum"
   ,COUNT("Items")   "Count"
FROM
   "table"
WHERE
   YEAR("Datetime") = 2017
GROUP BY
   MONTHNAME("Datetime")
ORDER BY
   MONTH("Datetime") DESC
 Edit: If one needs month names in some langauge other than English 
Having a table "Months" with ID (1 to 12) and 12 names:

Code: Select all

SELECT
   "Months"."Name" "Month"
   ,SUM("Revenue")   "Sum"
   ,COUNT("Items")   "Count"
FROM
   "table","Months"
WHERE
   YEAR("Datetime") = 2017 AND MONTH("Datetime") = "Months"."ID"
GROUP BY
  "Months"."Name"
ORDER BY
   "Months"."ID" DESC
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mehorter
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

Re: Sort by MONTH but display by MONTHNAME

Post by mehorter »

For what it's worth, I run HyperSQL Database Engine (HSQLDB) 2.3 in "split-mode" as detailed in viewtopic.php?f=83&t=65980.

I have tried the SELECT and GROUP BY MONTHNAME but the ORDER BY MONTH throws an error:

SQL Status: 42576
Error code: -5576

invalid ORDER BY expression
LibreOffice Version: 6.1.3.1 on Linux4.18.6-1-default x86_64 GNU/Linux Open Suse Tumbleweed
mehorter
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

Re: Sort by MONTH but display by MONTHNAME

Post by mehorter »

However, if I also SELECT MONTH and GROUP BY MONTH this works although I do get the extra column MONTH. I can deal with that, no biggie, but is there a way to avoid that?

Code: Select all

SELECT 
   MONTH("Datetime") "Month"
   ,MONTHNAME("Datetime") "Monthname"
   ,SUM("Revenue")   "Sum"
   ,COUNT("Items")   "Count"
FROM 
   "table" 
WHERE 
   YEAR("Datetime") = 2017
GROUP BY 
   MONTHNAME("Datetime")
   ,MONTH("Datetime")
ORDER BY 
   MONTH("Datetime") DESC
this returns:

Code: Select all

Month  |Monthname   | Sum   | Count
11     |November    |  100  |  5
10     |October     |  101  |  6
This is acceptable. Thank you very much.
Should I mark this "Solved" at this point?
LibreOffice Version: 6.1.3.1 on Linux4.18.6-1-default x86_64 GNU/Linux Open Suse Tumbleweed
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort by MONTH but display by MONTHNAME

Post by Villeroy »

You don't need to select the month number. You can sort and group by any column of the underlying row set, visible or not.
The underlying row set is determined by the FROM clause.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mehorter
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

Re: Sort by MONTH but display by MONTHNAME

Post by mehorter »

Yes, of course! Sometimes another set of eyes helps one avoid the obvious barrier in our path.

Solved!
LibreOffice Version: 6.1.3.1 on Linux4.18.6-1-default x86_64 GNU/Linux Open Suse Tumbleweed
Post Reply