I have this query: Which has this result: However, I’d like to add order_total and item_total in order to get just total. So I’d expect this: Doing this did not work: Another monkeywrench is that the type of the numbers is money not integer. Anyone would be able to help? Answer If any of the 2 sums returns NULL then
Tag: postgresql
Improve Query to not have to run multiple times?
I currently have this working query. However, I have to run it multiple times with different parameters to get the total result. I’m wondering if there’s a way I can run a loop or create a column for each query within one query so I can get the complete result without commenting in specific params and then commenting out (Running
Get Value in Date Range with date parameter
Given 2 tables: Table A Table B I want to produce the following View: My Question, how to get Table A Value inside of that view, based on that transaction date within Table A Date From and Date To? Answer This query will match each table_a entry to every table_b transaction, based only on the transaction date being BETWEEN datefrom
Postgres duplicate column based on value in column
I’ve the following table in postgresql id product customer price amount 1 Television Alfred 12.00 1 2 Radio Richard 20.00 3 3 Mobile Richard 40.00 5 Is there some way via SQL to duplicate the line n-times in case the amount is greater than one? This is how the table should look like after running the query: id product customer
How to make POSTGRESQL query based on first 2 parameters and latest third parameter
I have DB: I need to make query request for last year(or last 12 months) group by Client_id, Version and latest date(!) for them. For example: This is what I have right now: And I’m getting result for EVERY DAY. If I’m removing DATE from group by, its complaining that Date should be in Group by. I hope I did
Subquery must have alias despite having alias?
I have an Alias at the bottom (AS crypto). However, I still get the “subquery in FROM must have an alias” error. And then HINT “For example, FROM (SELECT …) [AS] foo.” I see many other posts that say to “just add AS _” as an alias, but mine still isn’t working. What am I doing wrong? Answer Remember: We
Sequelize raw query update array of objects as replacements
I am using sequelize (postgres) and I need to properly escape a query like this: Sample input.pets: Does anyone have an idea how to achieve this with replacements? I have found a thread on github which suggested something like this: However, a 2d array is being used here not an array of objects. Is there a way to access individual
Why a partitioned table size is larger than a normal table size with the same data
I insert the same data into two tables: one is partitioned, the other is normal. I use the following command to determine the size of the normal table: The output is: 6512 MB I use the following command to determine the size of the partitioned table: The output is: 6712.1 MB The partitioned table has 1001 partitions. Both tables have
Postgres giving relation does not exist error for alias
Above query is working fine in MS Sql but showing this error when running it in postgres Answer In MS Sql Server it’s possible to update the CTE. In Postgresql you can link to a CTE for an update. For example: However, such update assumes that the id in that table is unique. But, to mark the newest duplicates it
Turn one column into multiple
Im trying to turn a single column of data, id 1 2 3 4 5 6 into: col1 col2 col3 1 2 3 4 5 6 I figured it would be something to do with pivots but I’m still unsure how to accomplish this. Answer We can try using a pivot query with the help of ROW_NUMBER: Demo