I have these two tables; trips id date revenue 1 01/01/2020 5000 2 01/01/2020 3000 3 02/01/2020 4000 4 02/01/2020 2000 expenses id tripid amount 1 1 500 2 1 300 3 2 400 4 2 200 5 2 700 I would like to get the sum of revenue collected in a day AND sum of expenses in a day.
Tag: sql
Postgresql – Compare a string with a null value
I’m a bit puzzled because I believe the answer to this question is fairly simple but I’ve searched and tried several options and couldn’t find the right answer. The database is a PostgreSQL 13.1 I am using an API which sends a JSON object to a stored function in the database as follows: The …
Display the total amount of each products below the table
I’m using itextpdf to display the sql query I was able separate the table according to PROD_NAME, and calculate the total amount of each products by getting the sum(amt) over(partition by prod_name) as total_amt from the sql query. Now, I’ve been trying to display the total amount of each products…
sys.tables vs COUNT
I have a variable @tableName (NVARCHAR(255)) that is name of a table. I’m trying to perform an action if data exists in the the table. There are two ways to check this. Method #1 (bulky) Method #2 (clean) Are methods #1 and #2 doing the exact same thing? Is it safe to use method #2? Can there be case wh…
Why to use NoSQL DB when we could scale out SQL DB by sharing?
I recently read that the NoSQL DB scale out more easily than the traditional SQL DB. But consider that I have a huge table in SQL DB then I could easily distribute rows among multiple servers based on some hash function. For e.g. – If I have 4 servers I could do (id%4) where id is the primary key of
Data Structure for One Entry with Unknown Number of Tags
Noob here. I am trying to create a class or data structure where each entry has one unique name and an unknown number of descriptive tags. I’d like the data structure to be something I could also easily export to some common format (I am guessing that CSV would not be able to do what I am asking since I
TRIM or REPLACE in Netsuite Saved Search
I’ve looked at lots of examples for TRIM and REPLACE on the internet and for some reason I keep getting errors when I try. I need to strip suffixes from my Netsuite item record names in a saved item search. There are three possible suffixes: -T, -D, -S. So I need to turn 24335-D into 24335, and 24335-S …
How can I use a SET variable concisely in a DROP/CREATE/INSERT statements in PostgreSQL?
I have a template of a migration script that we run with new version when needed. It’s something like: As you can see, the myapp.user value (someuser) is used at three places in the script. Is there a more elegant way to use myapp.user in the INSERT? How can I use myapp.user in the DROP TABLE and the CR…
Trigger before insert in PostgreSQL and except some columns
I’m trying to write a trigger for my table. This table has 50 columns and 3 of them are timestamp type. In the future I will insert new rows and they can be duplicate of existing, so I need to compute hash of each row. My idea is to compute row’s hash in each insertion and check it’s existin…
How to merge two tables and sum the values with the same ID in SQL?
I am new to SQL sorry if the question ends up being trivial. I have two tables: Table 1 “Clients”: Table 2 “Payments”: Desired Output: Here is what I have tried: This sums the entries in table “payments” with the same ID. However, I don’t know how to use the ID in ord…