0

This query works, but uses TO_CHAR(date_created, 'YYYY') three times.

Is there a way to simplify this query?

SELECT TO_CHAR(date_created, 'YYYY'), count(*) 
FROM ticket 
WHERE date_created >= '2013-01-01' and 
      date_created <= '2017-06-01' 
GROUP BY TO_CHAR(date_created, 'YYYY') 
ORDER BY TO_CHAR(date_created, 'YYYY');

Result:

 to_char | count  
---------+--------
 2013    | 441500
 2014    | 574660
 2015    | 247910
 2016    | 601234

I want to simplify it. Performance does not matter at all (in this case). Unfortunately I found no matching tag for this question. I took "optimization".

I use PostgreSQL

  • Which RDBMS are you using? (TO_CHAR suggests Oracle) – Philᵀᴹ Oct 13 '16 at 8:12
  • @Philᵀᴹ I updated the question. – guettli Oct 13 '16 at 8:13
  • 4
    Unrelated, but: I prefer using extract(year from date_created) that way the "year" is really a number, not a character string – a_horse_with_no_name Oct 13 '16 at 8:51
  • 1
    Another handy function is date_trunc. – salva Oct 13 '16 at 9:03
  • Why do you want to "simplify" it? It's a pretty simple query. You don't need to use the expression (TO_CHAR(date_created, 'YYYY')) three times, only 2. If you alias the column (say year_created) in the select list, you can use it in ORDER BY, with ORDER BY year_created. – ypercubeᵀᴹ Oct 13 '16 at 9:09
1

A CTE can be used in this case:

WITH yourdata as (
    SELECT TO_CHAR(date_created, 'YYYY') AS tc_date_created
    FROM ticket
    WHERE date_created >= '2013-01-01' AND 
          date_created <= '2017-06-01' 
)
SELECT tc_date_created, count(*)
FROM yourdata 
GROUP BY tc_date_created
ORDER BY tc_date_created;
  • 1
    Or extract(year from date_created) as they are in Postgres ;) – ypercubeᵀᴹ Oct 13 '16 at 8:30
  • @ypercubeᵀᴹ: extract() is not only used in Postgres. It's standard SQL and also supported by other DBMS. – a_horse_with_no_name Oct 13 '16 at 8:53
  • @a_horse_with_no_name the original question was not tagged and Phil assumed it might be Oracle. Not sure if Oracle has extract(). – ypercubeᵀᴹ Oct 13 '16 at 9:03
  • @ypercubeᵀᴹ: Oracle does support the standard extract() function – a_horse_with_no_name Oct 13 '16 at 9:04
  • Great thnx. I didn't know/ – ypercubeᵀᴹ Oct 13 '16 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.