CODEWITHSUNDEEP
sqljoingroup-bycount
Gabriel
Gabriel

Reputation: 386

SQL - Query that returns the Username along with their total count of records

I'm new to the relational database stuff and Im having a hard time understanding how to write a query to do what I want. I have two tables that have a relationship.

CREATE TABLE DocumentGroups (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          comments TEXT,
          Username TEXT NOT NULL,
      )   

CREATE TABLE Documents (
        id INTEGER PRIMARY KEY,
        documentGroupId INT NOT NULL,
        documentTypeId INT NOT NULL,
        documentTypeName TEXT NOT NULL,  
        succesfullyUploaded BIT
      )

I would like to query the Documents table and get the record count for each username. Here is the query that I came up with:

SELECT Count(*)
FROM DOCUMENTS 
JOIN DocumentGroups ON Documents.documentGroupId=DocumentGroups.id 
GROUP BY Username

I currently have 2 entries in the Documents table, 1 from each user. This query prints out:

[{Count(*): 1}, {Count(*): 1}]

This looks correct, but is there anyway for me to get he username associated with each count. Right now there is no way of me knowing which count belongs to each user.

Upvotes: 0

Views: 1160

Answers (1)

GMB
GMB

Reputation: 222432

You are almost there. Your query already produces one row per user name (that's your group by clause). All that is left to do is to put that column in the select clause as well:

select dg.username, count(*) cnt
from documents d
join documentgroups dg on d.documentgroupid = dg.id 
group by dg.username

Side notes:

  • table aliases make the queries easier to read and write

  • in a multi-table query, always qualify all columns with the (alias of) table they belong to

  • you probably want to alias the result of count(*), so it is easier to consume it from your application

Upvotes: 1

Related Questions