CODEWITHSUNDEEP
sqlms-access
Peter
Peter

Reputation: 21

SQL - Count without grouping

Since 3 hours I am trying to count different columns without grouping them. I tried it with join or sub-select queries, but as I am new in SQL, I did not solve how to do it.

So here is my SQL query:

SELECT [Destination ID], 
       [ProjectID], 
       [Campaign ProjectId]
FROM Raw_Data;

How can I count the Destination ID, Project ID and the Campaign Project ID in one SQL Query and also show every ID in the Table.

Here one example, how the output should look like:

Screenshot

I am using MS Access.

It would be great, if some could help me.

Thanks a lot!

Upvotes: 0

Views: 136

Answers (1)

Nik Bo
Nik Bo

Reputation: 1410

You could try this, if I understood you right:

SELECT 
    [Destination ID], 
    (SELECT COUNT([Destination ID]) 
        FROM Raw_Data AS T2 
        WHERE T2.[Destination ID] = T1.[Destination ID]) AS DestCount,
    [ProjectID], 
    (SELECT COUNT([ProjectID]) 
        FROM Raw_Data AS T3 
        WHERE T3.[ProjectID] = T1.[ProjectID]) AS ProjectCount,
    [Campaign ProjectId],
    (SELECT COUNT([Campaign ProjectId]) 
        FROM Raw_Data AS T4 
        WHERE T4.[Campaign ProjectId] = T1.[Campaign ProjectId]) AS CampaignCount 
FROM Raw_Data AS T1;

EDIT

With newer Access versions you could try:

SELECT 
    T1.[Destination ID], 
    T2.[DestCount],
    T1.[ProjectID], 
    T3.[ProjectCount],
    T1.[Campaign ProjectId],
    T4.[CampaignCount] 
FROM (((Raw_Data AS T1 
        INNER JOIN 
        (
        SELECT [Destination ID], COUNT([Destination ID]) AS DestCount 
        FROM Raw_Data 
        GROUP BY [Destination ID]
        ) 
        AS T2 ON 
        T1.[Destination ID] = T2.[Destination ID])
        INNER JOIN 
        (
        SELECT [ProjectID], COUNT([ProjectID]) AS ProjectCount
        FROM Raw_Data
        GROUP BY [ProjectID]
        )
        AS T3
        ON T1.[ProjectID] = T3.[ProjectID])
        INNER JOIN 
        (
        SELECT [Campaign ProjectID], COUNT([Campaign ProjectID]) AS CampaignCount
        FROM Raw_Data
        GROUP BY [Campaign ProjectID]
        )
        AS T4
        ON T1.[Campaign ProjectID] = T4.[Campaign ProjectID])

I can't test this on newer Access versions, just read that this could work.

Upvotes: 1

Related Questions