Query Frustrations

Hi i got this query

SELECT COUNT(dbo.Table1.activity) AS TOTAL, dbo.Table2.name
FROM dbo.Table1 LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.activity = dbo.Table2.type
WHERE (dbo.Table1.activity = 2)
GROUP BY dbo.Table2.name

#-------------------------
and the result is this.....

name TOTAL
Not Sold 12179
Hangup 12179
Doesn't Want to Give ACH 12179
Doesn't Want to Give CC 12179
#-------------------------

what chages should i make in my query to have a result like this....

name TOTAL
Not Sold 13
Hangup 300
Doesn't Want to Give ACH 25
Doesn't Want to Give CC 30

here's my table definitions
Table1
---
activity
2
3
2
3
5

Table2
-------
type | name
2 | Not Sold
3 | Blah
2 | Hangup
2 | Doesn't Want to Give ACH
5 | Do Not Call
2 | Doesn't Want to Give CCType needs to be unique in Table2.sql

Query for Tables in a Database...

Good Morning

Shopping for help writing a query for my VB Program to execute against
SQL Server. Here is what I have so far:

SELECT name
FROM sysobjects
WHERE (xtype = 'U')

Is there a way to add to that query to get the list of tables that have the properties COST and PARTNUMBER?

Thanks,
EdUSE Northwind
GO

SELECT *
FROM INFORMATION_SCHEMA.Tables t
WHERE EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Columns c
WHERE t.TABLE_NAME = c.TABLE_NAME
AND c.COLUMN_NAME IN ('Parts','Cost'))
GO|||thanks...

It took me a bit to translate your guidance into what SQL Server wanted.
I ended up with:

select name from sysobjects t where exists( select * from syscolumns c where t.id = c.id
and c.name in ('PartNumber', 'Cost'))
order by name

Thanks,
Ed|||Did you just try and cut and paste the code?

Look up INFORMATION_SCHEMA in Books online...

You want to use those over the system tables.

These are views, which use the system tables...|||IC what you mean now...

Here is what we have now.

SELECT distinct A.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
and b.column_name in ('Partnumber', 'Cost')
ORDER BY A.TABLE_NAME

Thanks,
Ed|||Obviously you found out that the join will produce dupTablenames (because of the columns)...why didn't you just use what I gave you?|||the first time I tried it, no results were given. Thought I had to do some
heeby geeby to get it ready for use for my database. But then I noticed
the Parts column reference instead of Partnumber. Changed that and now it runs and returne the proper results.

Thanks,
ED