6 Replies - 753 Views - Last Post: 22 June 2018 - 04:38 PM Rate Topic: -----

#1 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

is it possible to make division from 3 sub-queries?

Posted 21 June 2018 - 06:14 PM

So.. I made 3 sub-query and each sub-query is count of total # of specific value that is only available when I join 3 different tables together. So.. when I had 2 sub-query, i made it work but now I have to make division of sub-query 1 by (sub 2 + sub 3) and it's not working well.. is there a way to make this thing easier?

Something like
select (subquery1) as total_aa, (subquery2) as total_bb, (subquery) as total_cc, (total_aa/(total_bb + total_cc)) as something
from table group by 1;

or something?

I am lost.. each subquery is like 20 lines long and I am putting it on C# program so all these "" makes me even more confusing..

This post has been edited by shin777: 21 June 2018 - 06:18 PM


Is This A Good Question/Topic? 0
  • +

Replies To: is it possible to make division from 3 sub-queries?

#2 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5457
  • View blog
  • Posts: 14,406
  • Joined: 18-April 07

Re: is it possible to make division from 3 sub-queries?

Posted 21 June 2018 - 09:00 PM

Well the sub queries can be their own tables with columns and then you should be able to do the math based on the columns. For instance assume that a subquery is your count...

select count(*) as count1 from sometable



Even though this is going to give us a single number, it is really still a table. One column table with one row. So we can use this in the FROM section.

select count1 FROM (select count(*) as count1 from sometable) as table1



Here we are selecting our column, count1, from the table we have called "table1". Table1 is made up of our sub query. So now you can apply this to the other sub queries.

select table1.count1, table2.count2, table3.count3 FROM (select count(*) as count1 from sometable) as table1, (select count(*) as count2 from sometable) as table2, (select count(*) as count33 from sometable) as table3



Here we have queries forming three tables... table1, table2, table3 and we are selecting the single column from each. Now that we have three columns, we could use them in a calculation no problem.

select table1.count1, table2.count2, table3.count3, (table1.count1 / (table2.count2 + table3.count3)) as fullcalculation FROM (select count(*) as count1 from sometable) as table1, (select count(*) as count2 from sometable) as table2, (select count(*) as count33 from sometable) as table3



The result of this should be four columns. One for each count and then our calculated column called fullcalculation. So see if this helps solve the problem you are having.

One last note I want to mention... when you get into doing more complex calculations, it is ok to bring that into the world of C#. Databases specialize in the storing and querying of record data, not that much as number crunching when it comes to more and more advanced calculations. Just make sure you pull only the data you need and have C# process them efficiently.
Was This Post Helpful? 0
  • +
  • -

#3 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: is it possible to make division from 3 sub-queries?

Posted 21 June 2018 - 09:27 PM

thanks. I got it to work but other ways around..

I am trying build a program where my group can access to total yield rate on our database with push of just one button. Of course, They have no knowledge of sql and even if they did, I can't give them an access to it.

It's just that this report I am building is massive. It's about 70 columns long in total and each cell contains sub-query value made up with 3-4 tables joined and most of them are count of hundreds of row result.

I wish I knew I can do this other than using C# but I only know little bit of sql and c#. :(/>/>/>

And yes, this is search engine so it suppose to pull full count and % on each product # in 1 long azz line. This seems a bit too much for c# but well.. it works... though total line count is like 1500+ with sub queries after sub queries

of data.

So far, I made it to pull the report when they type in product # in text box and push the button but now they want it to search by production date as well as product code.
lol.. that's gonna add another 3000 lines.. Not sure this is how it suppose to be when you build report like this but it is a pain in the butt.. Good way to kill a time at work though. lol


EDIT: Now.. that, I think about it.. if queries are this long.. would it be better if I just make procedure on this on sql and call it from c# instead of putting thousands of lines of sql queries on c# code?

This post has been edited by shin777: 21 June 2018 - 09:44 PM

Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: is it possible to make division from 3 sub-queries?

Posted 22 June 2018 - 08:50 AM

View Postshin777, on 21 June 2018 - 11:27 PM, said:

EDIT: Now.. that, I think about it.. if queries are this long.. would it be better if I just make procedure on this on sql and call it from c# instead of putting thousands of lines of sql queries on c# code?


Probably.
Was This Post Helpful? 0
  • +
  • -

#5 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5457
  • View blog
  • Posts: 14,406
  • Joined: 18-April 07

Re: is it possible to make division from 3 sub-queries?

Posted 22 June 2018 - 09:16 AM

I would concur with astonecipher. If you have tons of SQL lines, it really should be on the SQL server (if you can help it) in a stored procedure or similar. Would make it nicer for your C#. Depends on the queries through. Sometimes you just have to build it in C# because it is taking into account runtime data values etc that is not as simple as just passing values to a stored procedure.

:)
Was This Post Helpful? 0
  • +
  • -

#6 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: is it possible to make division from 3 sub-queries?

Posted 22 June 2018 - 04:23 PM

all these values are not stored in single cell from some table though. I have to join 3-4 different table and count total # of product that meets certain condition. Each of these tables are hundreds of lines long.. That is for just one cell.. I have to run similar queries for 70 columns long and some of these queries are finding percentage.. which means I have to make more complicated sub-queries to find values..

like round((((query1) + (query2))/(query3)), 4) * 100 to find % of something.. I put it together and it took like 5 mins to get result of these thousand line long query result report on c#. Is there a faster or better way to do this? :(
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: is it possible to make division from 3 sub-queries?

Posted 22 June 2018 - 04:38 PM

Doesn't matter, the database is still faster at processing database things. I have worked on stored procedures that may as well have been programs themselves, talking 10k lines long.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1