Solved

Tricky Group by with Sort?

Posted on 2008-06-13
5
171 Views
Last Modified: 2010-04-21
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
Comment
Question by:jfergy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 21784504
post all the relevant fields from each table. for eg
which table does PersonnelID and ListingagentID come from?
0
 

Author Comment

by:jfergy
ID: 21795634
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
 
LVL 19

Accepted Solution

by:
frankytee earned 110 total points
ID: 21799416
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
 

Author Closing Comment

by:jfergy
ID: 31467103
Thanks that was awesome!
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21800455
thanks, you're welcome
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Merge join vs exist 3 28
SQL query 7 20
SQL Server remove line breaks and tabbed 2 9
SQL Query logic question 14 29
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question