Helpful SQL Queries for Web Access Counts in Ares

Hi friends,

Back with some more helpful queries for gathering stats. You can use these to help construct additional reports in AtlasBI or use an ODBC link to run your own queries in Microsoft Access or other BI software. If you have questions or get stuck, send requests to support@atlas-sys.com and we'll do out best to help you out. Happy counting!

 --counts total for semester--
SELECT Count (ih.entry) AS web_clicks
FROM   items i
       INNER JOIN courses c
               ON i.courseid = c.courseid
       INNER JOIN itemhistory ih
               ON i.itemid = ih.itemid
                  AND ih.entry = 'WebAccess'
WHERE  c.semester = 'Spring 2020'
       AND i.digitalitem = 1
       AND ih.datetime BETWEEN i.activedate AND i.inactivedate
GROUP  BY ih.entry


--counts by itemid--
SELECT Count (ih.entry) AS web_clicks,
       i.itemid
FROM   items i
       INNER JOIN courses c
               ON i.courseid = c.courseid
       INNER JOIN itemhistory ih
               ON i.itemid = ih.itemid
                  AND ih.entry = 'WebAccess'
WHERE  c.semester = 'Spring 2020'
       AND i.digitalitem = 1
       AND ih.datetime BETWEEN i.activedate AND i.inactivedate
GROUP  BY ih.entry,
          i.itemid
ORDER  BY Count (ih.entry) DESC


--counts by courseid--
SELECT Count (ih.entry) AS web_clicks,
       c.courseid
FROM   items i
       INNER JOIN courses c
               ON i.courseid = c.courseid
       INNER JOIN itemhistory ih
               ON i.itemid = ih.itemid
                  AND ih.entry = 'WebAccess'
WHERE  c.semester = 'Spring 2020'
       AND i.digitalitem = 1
       AND ih.datetime BETWEEN i.activedate AND i.inactivedate
GROUP  BY ih.entry,
          c.courseid
ORDER  BY  Count (ih.entry) desc


--counts by department--
SELECT Count (ih.entry) AS web_clicks,
       c.department
FROM   items i
       INNER JOIN courses c
               ON i.courseid = c.courseid
       INNER JOIN itemhistory ih
               ON i.itemid = ih.itemid
                  AND ih.entry = 'WebAccess'
WHERE  c.semester = 'Spring 2020'
       AND i.digitalitem = 1
       AND ih.datetime BETWEEN i.activedate AND i.inactivedate
GROUP  BY ih.entry,
          c.department
ORDER  BY c.department


--counts by itemformat--
SELECT Count (ih.entry) AS web_clicks,
       i.itemformat
FROM   items i
       INNER JOIN courses c
               ON i.courseid = c.courseid
       INNER JOIN itemhistory ih
               ON i.itemid = ih.itemid
                  AND ih.entry = 'WebAccess'
WHERE  c.semester = 'Spring 2020'
       AND i.digitalitem = 1
       AND ih.datetime BETWEEN i.activedate AND i.inactivedate
GROUP  BY ih.entry,
          i.itemformat
ORDER  BY i.itemformat


--counts by itemformat--
SELECT Count (ih.entry) AS web_clicks,
       i.documenttype
FROM   items i
       INNER JOIN courses c
               ON i.courseid = c.courseid
       INNER JOIN itemhistory ih
               ON i.itemid = ih.itemid
                  AND ih.entry = 'WebAccess'
WHERE  c.semester = 'Spring 2020'
       AND i.digitalitem = 1
       AND ih.datetime BETWEEN i.activedate AND i.inactivedate
GROUP  BY ih.entry,
          i.documenttype
ORDER  BY i.documenttype


--counts by courseid, department, documenttype, itemformat--
SELECT Count (ih.entry) AS web_clicks,
       i.documenttype,
       i.itemformat,
       c.department, c.courseid
FROM   items i
       INNER JOIN courses c
               ON i.courseid = c.courseid
       INNER JOIN itemhistory ih
               ON i.itemid = ih.itemid
                  AND ih.entry = 'WebAccess'
WHERE  c.semester = 'Spring 2020'
       AND i.digitalitem = 1
       AND ih.datetime BETWEEN i.activedate AND i.inactivedate
GROUP  BY ih.entry,
          i.documenttype,
          i.itemformat,
          c.department,
		  c.courseid
ORDER  BY c.courseid