Databases
StackOverflow2013
Postsdbo
POcounting records from a grouped query with additional criteria
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POcounting records from a grouped query with additional criteria
    primarykey
    data
    text
    <p>I have a table of reports that has these relevant fields:</p> <pre><code>user_id (int 11) submitted_date (datetime) approved_flag (shortint 1) </code></pre> <p>There are multiple rows per <code>user_id</code>, some with <code>approved_flag</code> = 0 and some with <code>approved_flag</code> = 1 and each with a unique <code>submitted_date</code>.</p> <p>I need to get a count of the approved and unapproved reports. But I only want to count the most recent submitted report per user (most recent <code>submitted_date</code>), regardless of <code>approved_flag</code>.</p> <p>So, if there was 4 records like this:</p> <pre> user_id, submitted_date, approved_flag 1, 2009-04-01 01:00, 1 1, 2009-04-01 02:00, 0 1, 2009-04-01 03:00, 1 (using this record) 2, 2009-04-02 01:00, 1 (using this record) </pre> <p>the count would be 2 approved and no unapproved, and if we had</p> <pre> user_id, submitted_date, approved_flag 1, 2009-04-01 01:00, 1 1, 2009-04-01 02:00, 0 1, 2009-04-01 03:00, 0 (using this record) 2, 2009-04-02 01:00, 0 2, 2009-04-02 02:00, 1 (using this record) </pre> <p>the count would be 1 approved (user id 2) and 1 unapproved (user id 1).</p> <p>To summarize one more time because it is not easy to describe: I want the total number of unique users in the table (regardless of # rows per user) and I want it broken down in to 'approved' and 'not approved' based on the <code>approved_flag</code> of the most recent report per user.</p> <p>Any help would be greatly appreciated, thanks!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USCraig Nakamoto
    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
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POcounting records from a grouped query with additional criteria
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POcounting records from a grouped query with additional criteria
      1. This table or related slice is empty.
      1. VTUpMod
    1. COam I understanding correctly that you would never have a count higher than one (per user)? and that your approved + unapproved counts would never be higher than one (per user)?
      singulars
      1. POcounting records from a grouped query with additional criteria
      1. USRyan Guill
 

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