CODEWITHSUNDEEP
sqlms-access
zima10101
zima10101

Reputation: 307

ms access sum distinct date but group by quarter

Need help with following query

At moment displays a list of users with "Work Hours" grouped by Yearly Quarter. I would also like to sum by worked Days - issue is that there can be more than one row for each day, so the sum on "onsitedate" is incorrect. I cant group by "day" beacuse the results need to show by yearly quarter.

sql

SELECT 
  username,
  DatePart ('q', [onsitedate]) AS [qtr],
  SUM(onsitehours),
  SUM(onsitedate)
FROM
  clocktable 
WHERE onsitedate BETWEEN #2013-01-01# AND #2013-12-31#
GROUP BY username, DatePart('q',[onsitedate]) 
ORDER BY username, DatePart('q',[onsitedate])

let me know if more info needed and TQ

UPDATE

as noted I should be using COUNT on sitedate line (typo)

SELECT 
  username,
  DatePart ('q', [onsitedate]) AS [qtr],
  SUM(onsitehours),
  **COUNT(onsitedate)**
FROM

however if two records exist for same date then i would like the count to be 1

example data is below:

username        onsitedate         onsitehours
-----------------------------------------------
bob             01/09/2013         10
bob             01/09/2013         2
fred            01/09/2013         12
jim             01/09/2013         10
jim             02/09/2013         5

required rs 

username    qtr    onsitedate_count         onsitehours_sum
--------------------------------------------------------- 
bob         3      1                        12
fred        3      1                        12
jim         3      2                        15

as you can see - although there are two records for bob on 01/09/2013 the onsitedate_count is still 1 because they are the same day

also tried following so that days are grouped first, but no joy

GROUP BY tech_name, DatePart('d',[on_site_date]), DatePart('q',[on_site_date])

Upvotes: 1

Views: 2450

Answers (2)

user2065377
user2065377

Reputation: 458

just don't SUM(onsitedate). If I understant your question corretly

SELECT 
 a.username,
 DatePart ('q', a.[onsitedate]) AS [qtr],
 COUNT(b.[onsitedate]) onsitedate_count,
 SUM(a.onsitehours)
FROM clocktable a
  join (SELECT username, [onsitedate])
        FROM clocktable GROUP BY username,[onsitedate]) b
    on a.username = b.username
WHERE a.onsitedate BETWEEN #2013-01-01# AND #2013-12-31#
GROUP BY a.username, DatePart('q',a.[onsitedate]), b.onsitedate_count
ORDER BY a.username, DatePart('q',a.[onsitedate])

Upvotes: 0

HansUp
HansUp

Reputation: 97111

First create a query to sum the onsitehours for each combination of username and onsitedate.

SELECT 
    username,
    DatePart ('q', [onsitedate]) AS [qtr],
    onsitedate,
    SUM(onsitehours) AS SumOfonsitehours
FROM
    clocktable 
WHERE onsitedate BETWEEN #2013-01-01# AND #2013-12-31#
GROUP BY username, DatePart('q',[onsitedate]), onsidedate;

Then use it as a subquery and count the dates and sum the hours again for each combination of username and qtr.

SELECT
    sub.username,
    sub.qtr,
    Count(sub.onsitedate) AS onsitedate_count,
    Sum(sub.SumOfonsitehours) AS onsitehours_sum
FROM
    (
        SELECT 
            username,
            DatePart ('q', [onsitedate]) AS [qtr],
            onsitedate,
            SUM(onsitehours) AS SumOfonsitehours
        FROM
            clocktable 
        WHERE onsitedate BETWEEN #2013-01-01# AND #2013-12-31#
        GROUP BY username, DatePart('q',[onsitedate]), onsitedate
    ) AS sub
GROUP BY sub.username, sub.qtr
ORDER BY sub.username, sub.qtr;

With your clocktable sample data, that query returns your requested output in Access 2007.

Upvotes: 2

Related Questions