SQL : How to reuse count(*) computed value?

Question

I'm using Postgres 9.4.

I have a select statement in my query that looks like this :

SELECT date_part('year'::text, c.date) AS yyyy,
    to_char(c.date, 'MM'::text) AS monthnumber,
    to_char(c.date, 'TMMonth'::text) AS monthname,
    l.id AS lineID,
    n.id AS networkID,
    l.name AS lineName,
    count(c.*) AS count,
    count(distinct(c.date)) AS number_of_journeys,

I would like to reuse the two last columns (the computed ones) to compute another column like this :

SELECT date_part('year'::text, c.date) AS yyyy,
    to_char(c.date, 'MM'::text) AS monthnumber,
    to_char(c.date, 'TMMonth'::text) AS monthname,
    l.id AS lineID,
    n.id AS networkID,
    l.name AS lineName,
    count(c.*) AS count,
    count(distinct(c.date)) AS number_of_journeys,
    count / number_of_journeys AS frequentation_moyenne

Is it possible to do so ?

Thank you

Most RDBMSs won't allow you to refer to a column alias within the same query. But you can just give it a shot if you want. By the way, don't use count (or any other reserved words) as an alias. I doubt that a query with count(c.*) as count will even execute successfully. — Andrew
– Andrew, Commented Jan 21, 2015 at 14:37
Thanks for the naming tip, that's dumb of me for sure to name this column count. — Mat
– Mat, Commented Jan 21, 2015 at 14:39

Christian Barron · Accepted Answer · 2015-01-21 14:37:54Z

1

Not in SQL Server, you would have to use one of these:

SELECT date_part('year'::text, c.date) AS yyyy,
to_char(c.date, 'MM'::text) AS monthnumber,
to_char(c.date, 'TMMonth'::text) AS monthname,
l.id AS lineID,
n.id AS networkID,
l.name AS lineName,
count(c.*) AS count,
count(distinct(c.date)) AS number_of_journeys,
count(c.*) / count(distinct(c.date)) AS frequentation_moyenne

OR

Select yyyy, monthnumber, monthname, lineID, networkID, lineName, count, number_of_journery, count / number_of_journeys AS frequentation_moyenne
from
(SELECT date_part('year'::text, c.date) AS yyyy,
to_char(c.date, 'MM'::text) AS monthnumber,
to_char(c.date, 'TMMonth'::text) AS monthname,
l.id AS lineID,
n.id AS networkID,
l.name AS lineName,
count(c.*) AS count,
count(distinct(c.date)) AS number_of_journeys)

answered Jan 21, 2015 at 14:37

Christian Barron

2,7551 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mat Over a year ago

First method is what I was already doing. Second method is what I was looking for I guess, however when benchmarking it against the first method, they both consume the same amount of time. Maybe my dataset is too small to see the difference. Thanks anyway !

Collectives™ on Stack Overflow

SQL : How to reuse count(*) computed value?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related