Q:

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

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

A:

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)

