COUNT subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

COUNT subquery

Author

Topic

vermilion
Starting Member

4 Posts

Posted - 2006-04-02 : 04:09:14

Trying to write query to count the total number of records with each Type A and Type B under some condition, as well as the total number of different codes represented in those records.

Desired output will consist of 3 columns: Type, Total#Records, and Total#Codes. There are 2 types A and B. Total#Records has been successfully established (ok). Total#Codes has not.

Column1 Column2 Column3
Type Total#Records Total#Codes
A ok ???
B ok ???

I was able to get a count of the total number of records, but not the total number of codes. I'm using two different tables on an inner join. I used
SELECT Type, Count(Records) AS Total#Records
FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.Code

I'm afraid of getting into too much detail to explain my issue without making this post too long. I'd be happy to correspond via email.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-02 : 06:38:26

[code]SELECT Table1.Type, Count(Table1.Records) as [Total#Codes], codes.[Total#Codes]
FROM Table1
INNER JOIN
(SELECT Code,Count(Code) as [Total#Codes] FROM Table2 GROUP BY Code) AS codes
ON Table1.Code = codes.Code
GROUP BY Table1.Type[/code]

rockmoose

vermilion
Starting Member

4 Posts

Posted - 2006-04-02 : 14:27:07

I appreciate the quick response! Still not working, though. Here's what I tried:

SELECT Table1.Type, Count(Table1.Type) AS TotalRecords, Count(Table2.Code) AS TotalCodes, Codes.[TotalCodes]
FROM Table1
INNER JOIN
(SELECT Code, Count(Code) AS [TotalCodes] FROM Table2 GROUP BY Code) AS Codes
ON Table1.Code = Codes.Code
WHERE (Table1.Type)="V8" OR (Table1.Type)="V13" AND (Table2.Rec)="100" AND (Table2.Date)=#1/1/2006#
GROUP BY Table1.Type;

Error: You tried to execute a query that does not include the specified expression 'TotalCodes' a part of an aggregate function.

What does Codes.[TotalCodes] do?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-02 : 16:52:31

Try;

SELECT	Table1.Type
	,Count(Table1.Type) AS TotalRecords
	,Count(DISTINCT Table1.Code) AS TotalCodes
FROM	Table2
	INNER JOIN Table1
		ON Table2.Code = Table1.Code
WHERE	((Table1.Type)="V8" OR (Table1.Type)="V13")
	AND (Table2.Rec)="100"
	AND (Table2.Date)=#1/1/2006#
GROUP BY Table1.Type

If you are mixing OR and AND operators in the WHERE clause, use parenthesis to make the logic clear.
A AND B OR C
is not the same as
A AND (B OR C)

rockmoose

vermilion
Starting Member

4 Posts

Posted - 2006-04-02 : 17:24:13

MS Access does not support DISTINCT aggregate functions, e.g. this statement is illegal: SELECT(DISTINCT Codes)...

I believe I need to write a subquery, like: FROM (SELECT DISTINCT Codes, etc.... FROM...)

But I tried this again:
SELECT Table1.Type, Count(Table1.Type) AS TotalRecords, Count(Table2.Code) AS TotalCodes, Codes.[TotalCodes]
FROM Table1
INNER JOIN
(SELECT Code, Count(Code) AS [TotalCodes] FROM Table2 GROUP BY Code) AS Codes
ON Table1.Code = Codes.Code
WHERE ((Table1.Type)="V8" OR (Table1.Type)="V13") AND (Table2.Rec)="100" AND (Table2.Date)=#1/1/2006#
GROUP BY Table1.Type;

This query now says: "Syntax error in FROM clause" and highlights the 2nd FROM.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-02 : 17:43:54

Sorry if I can't help you much on this Access issue.
I don't know if Access supports derived queries either.

Maybe you can construct two separate queries in Access and then join those.

rockmoose

vermilion
Starting Member

4 Posts

Posted - 2006-04-02 : 18:48:02

Thanks for the input, regardless....much appreciated. Will post a solution once I get it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources