I have sample data like below. I need to show the total sum from amount column irrespective of account column and need to get account which has maximum value in the amount column. I used the below script for the same. I am able to get the account which has maximum value in the amount column but total sum is
Combine two large multi-JOIN and GROUP BY queries
So I have one query that is performing one GROUP BY that requires a lot of joining to get it to perform aggregation of a row count basically Then there is a similarly structured query but performing a different aggregation on a row count. How would you nest the second query into the first query so that the fi…
How to get multiple rows by ID sql query?
example: Let’s say I want to grab all rows where ID= 1 && 2. How would I do so? Answer Let’s say I want to grab all rows where ID= 1 && 2 Don’t think of it in terms of human language. Think of it in terms of boolean logic. Expressions which resolve to true/false values, which…
Python – Printing output into a SQL Table
I have a python script that can extract the metadata from pictures in a directory. As of right now they go into a text file in table format and the current output looks like this: Here is the current script I have: The script opens the directory and for each photo it takes the filename, size, type, height, wi…
How to reduce # of columns SQL has to look through while joining 2 tables?
I’m joining two tables together using an inner join, but given that these tables are billions of rows long, I was hoping to speed up my query and find a way to reduce the columns the sql has to comb through. Is there a way to, in a join, only have sql search through certain columns? I’m understand…
What do you call the . syntax in SQL?
In many databases, when writing a SELECT query, you can provide the database name along with the table name in order to resolve any ambiguity the database might encounter in case there are multiple databases with the same table names. For example, let’s say the user currently logged into SQL Server has …
Print within the SQL Server Select query based on the condition
I have this query from the link here which works fine, only that I am adding include FK constraints ‘ON Delete/ Update cascade’ if delete/update_referential_action is 1. I am unable to achieve the desired result. Any help appreciated. Answer Ms Sql 2016 dose not support if statement in middle of q…
Access – appending from two sources using unique multi-field ID. 1 field in 1 source formatted differently with leading 0s or letter
I am making updates to a database and am stuck with this problem. Appending to a destination with a unique two-field ID of, for example, field1 and field2. One source table, a ODBC linked table, has field1 text with field size of 8. Second source table is linked xlsx file and is imported with field1 as text w…
Can I use WHERE with table field on right side of LIKE?
I have a SQL table that contains prefixes of strings that I would like to match against longer strings. Suppose the column containing the prefix is called prefix. I now want to do a simple search for all rows that prefix a longer string. My first thought is to use a query that is something like this: The poin…
SQL query return no rows
acid tran_id tran_date tran_particular tran_amt part_tran_type ab500 m1 01-01-2022 123:qwe 10 C ab500 m5 10-01-2022 124:qse 20 C ab500 m16 11-01-2022 123:pyh 10 D I have the above table named htd. I am trying to fetch the result where tran_particular is unique before the “:” Final output looking f…