webDeveloper webDeveloper - 6 months ago 17
SQL Question

Using multiple columns with counts in access database designer

I am trying to display several columns with different counts in a microsoft access query. It doesn't let me do certain things a normal query can b/c it has the sql design view.
I'd like to display
multiple single etc columns with their counts.

Note: the table names and attributes have been changed.

select (select count(*)as multiple from (select userId from dbo.Purchases
where userId is not null GRoup by userId having count(*)>1) x), (
select count(*)as single from (select userId from dbo.Purchases where
userId is not null GRoup by userId having count(*)=1) x );


if I do these separately I can display it, but I'd like to combine them into one query and one row. Is this possible?

select count(*)as multiple from (select userId from dbo.Purchases
where userId is not null GRoup by userId having count(*)>1) x)

Answer

It's very easy with 2 queries:

First one, saved as "Purchases Summary"

Select UserID, count(UserID) as Count from Purchases Group By UserID

With a 2nd built on it:

SELECT Sum(IIf([count]=1,1)) AS [Single], Sum(IIf([count]>1,1)) AS Multiple FROM [Purchases Summary]

I cannot find a clever way to combine this into a single query.

I don't know what my problem last night was, but the single query is

SELECT Sum(IIf([count]=1,1)) AS [Single], Sum(IIf([count]>1,1)) AS Multiple
FROM (Select UserID, count(UserID) as Count from Purchases Group By UserID)