0

I need to get table, that contain changes of company income after any new order, dividing into genres. Table should look like:

+-------+---------+--------+
| Genre | orderid | income |  
+-------+---------+--------+
| 1     | 1       | 1500   |
| 1     | 2       | 2800   |  
| 2     | 1       | 2200   |  
| 3     | 1       | 700    |  
| 3     | 3       | 1200   | 
+-------+---------+--------+

Where Genre1-GenreN is all genres, Order1-OrderN - all orders

2800-summ of 1500(orderid 1) and 1300(orderid 2)

1200-summ of 700(orderid 1) and 500(orderid 3)

Now i have query, which shows the income for 1 order:

select g.title, round(ol.quantity*b.price) as genreincome from genre g
join book b on b.genre = g.title
join orderlist ol on ol.bookid = b.bookid
join orders o on o.orderid = ol.orderid
where o.orderid = 1

Query output: [Output]

1

eshirvana
  • 23,227
  • 3
  • 22
  • 38
Dan Malazia
  • 1
  • 1
  • 3
    Edit your question and show sample data. Also include your query as text in the question. – Gordon Linoff Apr 15 '21 at 14:57
  • Does this answer your question? [Dynamic pivot query using PostgreSQL 9.3](https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3) – astentx Apr 15 '21 at 15:06
  • Normalised structure would have the columns `genre, orderid, income` *(one row per order/genre combination)* rather than a separate column for each `orderid`. In fact, if you don't know how many columns you want at the time you write the code, you need code that reads the table to find out how many columns you need, then writes a new query with that many columns *(dynamic SQL)* and ***that*** is generally a serious code-smell / sql-anti-pattern. So, two significant questions are; is the number of orderid's fixed, and why do you want this particular layout? – MatBailie Apr 15 '21 at 15:07

0 Answers0