Databases
StackOverflow2013
Postsdbo
POms access sum distinct date but group by quarter
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POms access sum distinct date but group by quarter
    primarykey
    data
    text
    <p>Need help with following query</p> <p>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.</p> <p>sql</p> <pre><code>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]) </code></pre> <p>let me know if more info needed and TQ</p> <p>UPDATE</p> <p>as noted I should be using COUNT on sitedate line (typo)</p> <pre><code>SELECT username, DatePart ('q', [onsitedate]) AS [qtr], SUM(onsitehours), **COUNT(onsitedate)** FROM </code></pre> <p>however if two records exist for same date then i would like the count to be 1</p> <p>example data is below:</p> <pre><code>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 </code></pre> <p>required rs </p> <pre><code>username qtr onsitedate_count onsitehours_sum --------------------------------------------------------- bob 3 1 12 fred 3 1 12 jim 3 2 15 </code></pre> <p>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</p> <p>also tried following so that days are grouped first, but no joy</p> <pre><code>GROUP BY tech_name, DatePart('d',[on_site_date]), DatePart('q',[on_site_date]) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USzima10101
    1. USzima10101
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POms access sum distinct date but group by quarter
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHave you tried using `COUNT(onsitedate)` instead of `SUM(onsitedate)`?
      singulars
      1. POms access sum distinct date but group by quarter
      1. USGord Thompson
    2. COHi, COUNT is what i should be using, however this produces an incorrect result - if two records exist for same day the total will be 2, and I want the count to be 1 - have updated question
      singulars
      1. POms access sum distinct date but group by quarter
      1. USzima10101
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload