I have 3 doubts in SQL that I am not able to calculate the two aliases in a SQL query and make a condition.
I know this is not a paid community but I tried to learn this from other platforms but did not get the expected output that match to my issue.
I will understand if your coder will help me how to solve these three cases as below.
CASE 1: for subtracting the aliases SELECT type, (SELECT qty1 from table2 WHERE col2 = ?) AS qty1, (SELECT qty2 from table3 WHERE col3 = ?) AS qty2, (SELECT SUM(qty1) - SUM(qty2) as total_qty) from table1 WHERE col1= ? I am not able to subtract qty1 to qty2 as these two col names are aliases of table2 and table 3
CASE 2: When multiple aliases and one condition SELECT type, (SELECT qty1, qty2 from table2 WHERE col2 = 'value1' ) AS qty1 from table1 WHERE col1= ? qty1 and qty2 → two column and one condition in single select query
CASE 3: Finally condition on aliases SELECT type, (SELECT qty1 from table2 WHERE col2 = ?) AS qty1, (SELECT qty2 from table3 WHERE col3 = ?) AS qty2, (SELECT SUM(qty1) - SUM(qty2) as total_qty) from table1 WHERE col1= ? AND (qty1 = 1 AND qty2 = 1) got the aliases qty1 and qty2 and after add condition based on alises output in end of the query
What’s the relation between table1, table2, and table3? You’re probably overthinking something that can be written better as a single JOINed query…
I am using multiple select methods in one Query without any relation and I am also not using any joining to make relationship mandatory.
I am here getting two number value column and want to create last three column where the sum of two column value will show in column.
Well if you’re trying to get a sum, using a minus sign is probably not going to work…
So you’ve built a relational database out of unrelated things, and now want to do some math on them.
I feel like your database needs a better design.
That said, you seem to be trying to grasp the idea of subqueries as tables and putting them as fields. That doesnt really work without some form of relation; for example:
if table2 has qty’s
1
3
5
7
and table3 has qty’s
2
4
6
8
and they’re not related to each other, how do you expect the database to output rows? Rows imply relation. does table3’s 2 go with table2’s…1? 3? 5? 7? all of them? none of them?
You can take a SUM, and output that, certainly; you can incorporate multiple calculated values into a single row output, because it’s “the row of everything i asked for”.
So you could do:
SELECT table1.type, sumtable2.qty AS qty2, sumtable3.qty AS qty3, sumtable2.qty + sumtable3.qty AS total
FROM table1
JOIN (SELECT SUM(qty) as qty FROM table2 WHERE conditionfield = somevalue) as sumtable2
JOIN (SELECT SUM(qty) as qty FROM table3 WHERE conditionfield = somevalue) as sumtable3
WHERE table1.type = atypehere
This example counts a sum from table2, a sum from table3, and a total of the pair. It’s the closest i can get to matching what I think you’re trying to do with as close to your syntax as i could get.
Well case 2 you definitely cant do that way, because you’re trying to give 1 alias to 2 columns of data. So that makes no sense.
I’m not entirely sure what output you’re expecting from that, because again; table1 and table2 arent related. Let me give a data-driven example, and you can tell me what output you would expect.
table1 has one row in it.
type
col1
wark
moo
table2 has 5 rows in it.
qty1
qty2
col2
1
6
1
2
7
0
3
8
1
4
9
0
5
10
1
What exactly do you expect as output from your case 2 query?
I mean, you’ve still got the problem of matching data between rows of unrelated tables. This is why relational databases have relations; you can put the tables together in meaningful, predictable, and sensible ways. Saying “I’ve got apples and i’ve got oranges, combine and give me apporanges” doesnt… work. Either everything has to be reduced to a single row, or you need some form of relation between the two sets of data.