Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Tricky Group by with Sort?

Hello-

I have the following tables:
Personnel
ListingStats
Listings

I need to create a report for my personnel for their listings that gives them a break down of the stats and source count under each. So for example:

Listing 1234
 Google.com  154
 Frontdoor.com  25
Listing 1256
 Google.com 133
 Frontdoor.com 25

The listingStats table can have the source defined from anywhere on the web, so I wont be able to know what that source is, but I need to group by the source as a whole and the count. Your help is greatly apprecited. I have included the table breakdown below and possible query?

Select viewdate, source from tblListingStats where ListingID in(Select ListingID from tblListings where PersonnelID = ListingagentID) Groupby source, viewdate order by ListingID


0
jfergy
Asked:
jfergy
  • 3
  • 2
1 Solution
 
frankyteeCommented:
post all the relevant fields from each table. for eg
which table does PersonnelID and ListingagentID come from?
0
 
jfergyAuthor Commented:
tblListingStats: ID, ListingID, DateTime, Source
TblPersonnel: PersonnelID, FirstName, LastName
tblListings: ListingID, PersonnelID

PersonnelID to tblListings is a 1 to Many relationship
ListingID to tblListingStats is a 1 to many relationship

Thanks for the help!
0
 
frankyteeCommented:
try
select p.PersonnelID, l.ListingID,s.Source,
count(s.ID) as SourceCount
from TblPersonnel p join tblListings l
on p.PersonnelID = l.PersonnelID
join tblListingStats s
on l.ListingID = s.ListingID
group by p.PersonnelID, l.ListingID,s.Source

if that doesn't work then post sample data of each table and your query result set
0
 
jfergyAuthor Commented:
Thanks that was awesome!
0
 
frankyteeCommented:
thanks, you're welcome
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now