SQL counting 1 table and merging information from table 2 into the results

Question

I need help with SQL. I have a column in terminal and a column in table2 that match each other. (index) I want to display the count of terminals per index in table 'terminal', and merge it with table2 to include the index name in the output

select index,terminal=count(*) from terminal 
group by index

returns

index     terminal
--------- -----------
1          94
2          15

In table2 I have an index that matches the index in table 'terminal' and it also has the name that corresponds to the index.

Example:

select * from table2

Returns:

index    name          lcmid        handle     
------   ------        --------     --------
1        nameofindex1  8            10
2        nameofindex2  12           27

I want to count the number of terminals corresponding to the indexes in table 1 and then merge them to include the 'nameofindexes', lcmid and handle into the result. I apologize for sounding like a total newbie, but I'm learning as I go.

Any help is greatly appreciated.

willkil · Accepted Answer · 2014-02-06T23:13:28.493

0

Well, you need to join. Perhaps:

SELECT t1.index, c=count(*), t2.name FROM terminal AS t1
    INNER JOIN table2 AS t2 ON t1.index = t2.index
    GROUP BY t1.index;

I'm a little rusty, so that query may not be exactly right, but a JOIN is the direction you need to go.

EDIT:

As indicated in the comments, that works for mysql, but not for OP's flavor of SQL. Changing t1.index to t2.index in the SELECT and GROUP BY clauses worked:

SELECT t2.index, c=count(*), t2.name FROM terminal AS t1
    INNER JOIN table2 AS t2 ON t1.index = t2.index
    GROUP BY t2.index;

edited Feb 06 '14 at 23:13

answered Feb 06 '14 at 21:47

willkil

1,619
1
21
33

That doesn't work, it returns the wrong data. I get the c returning 346 (which is the correct count of terminals) but it shows 346 for all the lines returned in the output. – user3281308 Feb 06 '14 at 22:13
Yeah, I said I was rusty ;) ... I forgot your GROUP BY clause. I edited the answer to add that in. If everything else looked right, then I suspect this will work for you. – willkil Feb 06 '14 at 22:24
That's better, but it's still reporting the counts repetitively. In other words, there are approximately 30 unique indexes that corresponds to a name and there are X # of terminals on each index. It returns the correct # of terminals assigned to each index properly, but it repeats it for all 30 names associated to unique indexes. Example: Index 1 has 94 terminals and all 30 names are in a column on the output. 30 x (1), 30 x (94) and a unique name next to each `1 | 94 | name1` `1 | 94 | name2` `1 | 94 | name3` `1 | 94 | namen` `2 | 33 | name1` `2 | 33 | name2` – user3281308 Feb 06 '14 at 22:33
Interesting. I created a test database in mysql to see what was wrong with this query, and it appeared to work as you desire. Try changing t1.index to t2.index in both the SELECT and GROUP BY clauses to see how that works for your SQL flavor. What SQL are you using? – willkil Feb 06 '14 at 22:57
swapping t1.index to t2.index in both the SELECT and GROUP BY worked perfectly. Thank you for your HELP! I tried to upvote the answer, but it wouldn't let me. I'll just accept the answer and be done. I REALLY appreciate it. :D – user3281308 Feb 06 '14 at 23:07

SQL counting 1 table and merging information from table 2 into the results

1 Answers1