CODEWITHSUNDEEP
sqldatabasesum
nickhiebertdev
nickhiebertdev

Reputation: 563

Grouping Project Names and Total Hours together SQL JOIN ONs

So this generates Hours, Projects, Descriptions and Client names.

But the problem is that all the fields need to be grouped together if they're the same instead of displayed multiple times. I searched around and using the SUM function might work.

Here's the SQL statement:


SELECT h.hoursworked AS Hours, 
       p.projectname AS DocketName, 
       p.description AS Description, 
       p.archive AS Archived, 
       c.clientname AS Clients
FROM hours h 
JOIN projects p ON h.projectid = p.projectid 
JOIN clients c ON p.clientid = c.clientid
WHERE p.archive = 0
ORDER BY p.projectname ASC;

Upvotes: 2

Views: 763

Answers (3)

Siva
Siva

Reputation: 9101

Grouping can be done but if there are different combinations then grouping will only work to some extent.

Try this: Assuming all fields are Varchar and except hours

SELECT p.projectname AS DocketName, p.description AS Description, p.archive AS Archived, c.clientname AS Clients,Sum(h.hoursworked) AS Hours
    FROM hours h 
    JOIN projects p
    ON h.projectid = p.projectid 
    JOIN clients c
    ON p.clientid = c.clientid
    WHERE p.archive = 0
    ORDER BY p.projectname ASC;
Group by DocketName,Description,Archived,Clients

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You'll need to add a GROUP BY clause.

Try the following:

SELECT  SUM(h.hoursworked)  AS TotalHours
        , p.projectname     AS DocketName
        , p.description     AS Description
        , p.archive         AS Archived
        , c.clientname      AS Clients
FROM    hours       h 
JOIN    projects    p ON h.projectid = p.projectid 
JOIN    clients     c ON p.clientid = c.clientid
WHERE   p.archive = 0
GROUP BY p.projectname, p.description,  p.archive, c.clientname
ORDER BY p.projectname ASC

If you only want to see the Project Name and the Total Hours, you can do this instead:

SELECT  SUM(h.hoursworked)  AS TotalHours
        , p.projectname     AS DocketName
FROM    hours       h 
JOIN    projects    p   ON h.projectid = p.projectid 
JOIN    clients     c   ON p.clientid = c.clientid
WHERE   p.archive = 0
GROUP BY p.projectname
ORDER BY p.projectname ASC

Upvotes: 1

Susanna
Susanna

Reputation: 72

I'm not sure but what about SELECT DISTINCT?

Upvotes: -1

Related Questions