How to write an SQL report using distinct and sum

0
votes

I'm trying to write an SQL report that groups rows, removes duplicates, and sums up values in virtual columns.

I have this table


    make  | model  | warranty | price
   -------+--------+----------+-------
    Honda | Accord |        2 |   700
    Honda | Civic  |        3 |   500
    Lexus | ES 350 |        1 |   900
    Lexus | ES 350 |        1 |   900
    Lexus | ES 350 |        2 |  1300
    Lexus | ES 350 |        3 |  1800
   (6 rows)

I'm trying to create a report that adds two virtual columns, qty and total. Total is the sum of qty * price. The table should like the one below.

   qty | make  | model  | warranty | price  | total
  -------+--------+--------+----------+-------------
    1  | Honda | Accord |        2 |   700  |   700
    1  | Honda | Civic  |        3 |   500  |   500
    2  | Lexus | ES 350 |        1 |   900  |  1800
    1  | Lexus | ES 350 |        2 |  1300  |  1300
    1  | Lexus | ES 350 |        3 |  1800  |  1800
   (5 rows)
1
sqlpostgresqlreport

1 Answers

1
votes

I think this is simple aggregation:

select count(*) as qty, make, model, warranty,
       avg(price) as price, sum(price) as total
from t
group by make, model, warranty;