CODEWITHSUNDEEP
sql-serverexcel
user595985
user595985

Reputation: 1583

Subquery using Excel's Microsoft Query

I am trying to write a simple sub query that counts the number of unique alarm ID in a resultset after joining 2 tables. i.e

SELECT COUNT(DISTINCT ALARMTABLE.ID) FROM
 (SELECT ALARMTABLE.ID FROM ALARMTABLE) AS AA
JOIN
 (SELECT ALARMDESCRIPTIONTABLE.DESCRIPTION FROM ALARMDESCRIPTIONTABLE) AS BB
ON AA.ID=BB.ID

Both these tables have an id column that relates the id in one table with the description in the other. The idea being i can get a count and description for each alarm. e.g.

AlarmDescription     Alarm COUNT
-----------------------------------
Fire Alm                 1000
Flood Alm                12
Tornado Alm              75

When i run this using Microsoft query all i get is the error can't add table '('. What am i doing wrong?

Upvotes: 0

Views: 4997

Answers (2)

onedaywhen
onedaywhen

Reputation: 57073

Your SQL contains a few syntax errors.

I'm wondering if the SQL you are after could look more like this:

SELECT BB.DESCRIPTION, COUNT(*)
  FROM ALARMTABLE AS AA
       INNER JOIN ALARMDESCRIPTIONTABLE AS BB
          ON AA.ID = BB.ID
 GROUP 
    BY BB.DESCRIPTION;

Upvotes: 1

Andomar
Andomar

Reputation: 238246

First off, if you alias a table, it must be referred by alias name. So change:

SELECT COUNT(DISTINCT ALARMTABLE.ID) FROM

to

SELECT COUNT(DISTINCT AA.ID) FROM

Second, the error you get is probably when MS Query tries to visualize the SQL. You can work around that by adding a trivial query and editing it. Click properties, then the properties of the connection, then the Definition tab. Then you can edit the query in the Command Text field.

Or probably less painful: you could store the query in a view, and use the view in MS Query.

Upvotes: 0

Related Questions