How to add count column into query?

Question

I've got a query

WITH a as (
SELECT
      SECTION
    , MAX(PRICE) over w
    , MIN(PRICE) over w
    , AVG(PRICE) over w
    , TIME t, price
    , case when MAX(PRICE) over w = price then TIME end maxt
    , case when MIN(PRICE) over w = price then TIME end mint
FROM s154
WINDOW w as (partition by section)
)
select DISTINCT
      SECTION
    , MAX
    , MIN
    , AVG
    , max(maxt) over (partition by section)
    , min(mint) over (partition by section)
from a;

I decided to modify my table by adding new column into WITH:

count(*) FROM s154 GROUP BY section.

But adding group by clause asks for max and min in group by too. Is it possible to count sections in WITH part of the query?

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2017-06-25 12:20:34 +00:00
Commented Jun 25, 2017 at 12:20 — Gordon Linoff
– Gordon Linoff, Commented Jun 25, 2017 at 12:20

Gordon Linoff · Accepted Answer · 2017-06-25 12:32:16Z

You can just add count(*) over w:

WITH s as (
      SELECT SECTION, MAX(PRICE) over w as max_price,
             MIN(PRICE) over w as min_price, AVG(PRICE) over w as avg_price, 
             TIME as t, price,
             (case when MAX(PRICE) over w = price then TIME end) as maxt
             (case when MIN(PRICE) over w = price then TIME end) as mint,
             (COUNT(*) over w) as cnt
      FROM s154 WINDOW w as (partition by section)
     )
select DISTINCT SECTION, max_price, min_price, avg_price,
       max(maxt) over (partition by section),
       min(mint) over (partition by section),
       cnt
from s;

I'm pretty sure this query can be simplified. I added a few things so it is easier to follow:

Explicit column aliases. Name your own columns, they are important.
as before the column alias, so I can tell where the name is.
Meaningful CTE name. "a" is meaningless in this context. At least "s" is an abbreviation for the table.

I think the simpler version is:

      SELECT SECTION, MAX(PRICE) as max_price,
             MIN(PRICE) as min_price, AVG(PRICE) as avg_price, 
             (ARRAY_AGG(time ORDER BY price))[1] as time_at_min_price,
             (ARRAY_AGG(time ORDER BY price DESC))[1] as time_at_max_price
      FROM s154 
      GROUP BY section;

This seems like a much better way to express the logic.

Collectives™ on Stack Overflow

How to add count column into query?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related