Equivalent to Joining tables (SQL) in Excel?

  • Thread starter WWGD
  • Start date
  • #26
BWV
1,047
1,119
But if your query is not aggregating properly and there are a number of tables, the likely culprit is a one-to-many join
 
  • #27
WWGD
Science Advisor
Gold Member
5,715
5,978
But if your query is not aggregating properly and there are a number of tables, the likely culprit is a one-to-many join
I think the problem may be related to the variable defined as a ratio of aggregates. Aggregates are messy in SQL Server, more so if we use joins as well. I am double-checking. This is the reason i tried Excel, because the ration I did : Sum(A)/Count (B) , all done over a common field in two databases, was not outputting the right results.
 
  • #28
BWV
1,047
1,119
You can nest queries in SQL server so if you can do two separate queries with the correct results they can be combined
 
  • #29
WWGD
Science Advisor
Gold Member
5,715
5,978
You can nest queries in SQL server so if you can do two separate queries with the correct results they can be combined
You're right, those do take some extra work. Will try it , tho. Thanks for all your comments.
 
  • #30
WWGD
Science Advisor
Gold Member
5,715
5,978
Ah, never mind. In case anyone is interested for the SQL format:
We want to compute the ratio SUM(f_1)/COUNT(f_2) , f1 in table 1 (T1), f2 in table 2 (T2);
f_12 a common field , over values in f_12 common to T1, T2.
This runs: EDIT

SELECT A/B FROM
( SELECT SUM(f_1) AS A
FROM T1 JOIN T2 on T1.f_12=T2.f_12 GROUP BY f_12) AS Table1
JOIN
(SELECT COUNT(f_2) AS B FROM T1 JOIN T2 ON T1.f_12=T2.f_12
GROUP BY f_12 ) AS Table2
ON Table1.f_12 =Table2.f_12
GROUP BY f_12

Sorry to all for dragging this along so far.
 
Last edited:
  • #31
1
1
Ah, never mind. In case anyone is interested for the SQL format:
We want to compute the ratio SUM(f_1)/COUNT(f_2) , f1 in table 1 (T1), f2 in table 2 (T2);
f_12 a common field , over values in f_12 common to T1, T2.
This runs: EDIT

SELECT A/B FROM
( SELECT SUM(f_1) AS A
FROM T1 JOIN T2 on T1.f_12=T2.f_12 GROUP BY f_12) AS Table1
JOIN
(SELECT COUNT(f_2) AS B FROM T1 JOIN T2 ON T1.f_12=T2.f_12
GROUP BY f_12 ) AS Table2
ON Table1.f_12 =Table2.f_12
GROUP BY f_12

Sorry to all for dragging this along so far.

Have you found a solution yet?
I do sql in Excel now and then (ODBC) using some time sheets in the same file as tables, but also sometime sheets in differents files.
I know you can do simple join operations. See this link with some .xlsx examples and a ReadMe.txt file:
https://www.dropbox.com/sh/awoes8lgpj76igb/AABZOAEq7pF36kIXKykn3PKga?dl=0

In file T_Queries, I put A and B in different columns and did A/B the old school Excel way in a formula but you can play around with more complex join queries and see how Excel handles it.. (Ribbon > Data > Connections > DoubleClick on Connection > Definition).
Those two queries use tables/sheets being in the same file (T_1_and_2.xlsx).

Another query, just for fun, uses tables/sheets being in 2 different files (T_1.xlsx and T_2.xlsx).
It's cool that one can use a virtual database of excel files being all over the place and query it using our dear friends SQL and ODBC!

Let us know it this helps a little in what you were trying to achieve.
 
Last edited:
  • #32
WWGD
Science Advisor
Gold Member
5,715
5,978
Have you found a solution yet?
I do sql in Excel now and then (ODBC) using some time sheets in the same file as tables, but also sometime sheets in differents files.
I know you can do simple join operations. See this link with some .xlsx examples and a ReadMe.txt file:
https://www.dropbox.com/sh/awoes8lgpj76igb/AABZOAEq7pF36kIXKykn3PKga?dl=0

In file T_Queries, I put A and B in different columns and did A/B the old school Excel way in a formula but you can play around with more complex join queries and see how Excel handles it.. (Ribbon > Data > Connections > DoubleClick on Connection > Definition)

Those two queries use tables/sheets being in the same file (T_1_and_2.xlsx).
Another query, just for fun, uses tables/sheets being in 2 different files (T_1.xlsx and T_2.xlsx).

Let us know it this helps a little in what you were trying to achieve.
Thanks. I was able to do it in SQL , but I had trouble using Microsoft query within Excel; somehow the connection was not working well ( despite the fact that the Wizard had no trouble sending data back-and-forth). Thanks for your refs., will check them out.
 

Related Threads on Equivalent to Joining tables (SQL) in Excel?

  • Last Post
Replies
8
Views
1K
  • Last Post
Replies
5
Views
2K
Replies
5
Views
2K
  • Last Post
Replies
12
Views
1K
  • Last Post
Replies
5
Views
2K
Replies
1
Views
807
Replies
8
Views
2K
  • Last Post
Replies
19
Views
2K
Replies
11
Views
899
  • Last Post
Replies
4
Views
4K
Top