sqldatabaseisql

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

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.

Solution

  • 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;