Jump to content
  • 0

count distinct in datapage based on a view



I need help figuring out how to use SELECT DISTINCT (or something similar) in a calculated field in a datapage based on a view. I am mostly unfamiliar with SQL and can't figure out how to implement the syntax.

Datapage is based on a 3-table view "LocusArtifactFragment" where Locus is parent table, Artifact is child1 (one-to-many relationship with parent Locus), and Fragment is Child2 (one-to-many relationship with parent Artifact). I want to count distinct related records from Parent (Locus) in both related tables in the view (Artifact = Child1 AND Fragment = Child2). The view includes columns for LocusID, ArtifactID, and FragmentID (pk in each table) plus the foreign keys that make the relationships.

I can easily count related records of fragments by counting FragmentID, but I need to count Distinct Artifact ID (because if I just count Artifact ID, I get equal numbers of fragmentID and artifactID, since artifactID appears for every related record in Fragment). This syntax from the howto site seems like it is more or less what I need, but I can't get it functional when I sub in my field and view names: "SELECT Count(Country) FROM Customers WHERE Country=’Canada’"

Any thoughts on how to implement this would be very welcome. I need to verify students' data entry is correct and complete before moving forward with other parts of the project. Many thanks.


Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

If it's a Reports DataPage, you can try the following

SELECT COUNT(ColumnName) FROM _v_LocusArtifactFragment WHERE ViewColumnName=target.[@field]

If it's a View, you will have to put _v_ at the beginning of the View Name when using SQL Statement.

the target just means it will get the value of that field of the specific row that the Calculated Field is in.

If you have multiple WHERE conditions, just use AND (i.e. ViewColumnName=target.[@field] AND ViewColumnName2=value AND... etc)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...