Solved

Only want to see Products once in View

Posted on 2006-06-10
17
198 Views
Last Modified: 2006-11-18
How would I ask this view to only show dbo.placements.product once?
Im trying to show hit stats for each product

SELECT     TOP 50 dbo.placements.id AS Expr3, dbo.placements.eventdate, dbo.placements.product, SUM(dbo.hits.hit) AS Impressions, dbo.placements.filename,
                      dbo.placements.fileurl, dbo.placements.videolink, dbo.placements.videotitle, dbo.hits.[getDate()]
FROM         dbo.categories INNER JOIN
                      dbo.placements ON dbo.categories.id = dbo.placements.cid INNER JOIN
                      dbo.publications ON dbo.placements.pid = dbo.publications.id INNER JOIN
                      dbo.type ON dbo.placements.tid = dbo.type.id INNER JOIN
                      dbo.hits ON dbo.placements.id = dbo.hits.placement_id
GROUP BY dbo.placements.id, dbo.placements.eventdate, dbo.placements.product, dbo.placements.filename, dbo.placements.fileurl, dbo.placements.videolink,
                      dbo.placements.videotitle, dbo.hits.[getDate()]
0
Comment
Question by:vonfranzken
  • 9
  • 7
17 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16877706
are you getting duplicate values
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 16877721
Hi vonfranzken,

SELECT     TOP dbo.placements.product, SUM(dbo.hits.hit) AS Impressions
FROM         dbo.categories INNER JOIN
                      dbo.placements ON dbo.categories.id = dbo.placements.cid INNER JOIN
                      dbo.publications ON dbo.placements.pid = dbo.publications.id INNER JOIN
                      dbo.type ON dbo.placements.tid = dbo.type.id INNER JOIN
                      dbo.hits ON dbo.placements.id = dbo.hits.placement_id
GROUP BY dbo.placements.product


Regards,

Patrick
0
 

Author Comment

by:vonfranzken
ID: 16877742
Yes duplicate values for product.

not quite patrick
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16877839
SELECT     TOP 50  DISTINCT dbo.placements.id AS Expr3, dbo.placements.eventdate, dbo.placements.product, SUM(dbo.hits.hit) AS Impressions, dbo.placements.filename,
                      dbo.placements.fileurl, dbo.placements.videolink, dbo.placements.videotitle, dbo.hits.[getDate()]
FROM         dbo.categories INNER JOIN
                      dbo.placements ON dbo.categories.id = dbo.placements.cid INNER JOIN
                      dbo.publications ON dbo.placements.pid = dbo.publications.id INNER JOIN
                      dbo.type ON dbo.placements.tid = dbo.type.id INNER JOIN
                      dbo.hits ON dbo.placements.id = dbo.hits.placement_id
GROUP BY dbo.placements.id, dbo.placements.eventdate, dbo.placements.product, dbo.placements.filename, dbo.placements.fileurl, dbo.placements.videolink,
                      dbo.placements.videotitle, dbo.hits.[getDate()]
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16877845
if the above is not working can you post the sample output u r getting while running the above
0
 

Author Comment

by:vonfranzken
ID: 16877856
Error is SELECT clause: expression near DISTINCT missing from clause
unable to parse query
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16877861
SELECT   DISTINCT  TOP 50   dbo.placements.id AS Expr3, dbo.placements.eventdate, dbo.placements.product, SUM(dbo.hits.hit) AS Impressions, dbo.placements.filename,
                      dbo.placements.fileurl, dbo.placements.videolink, dbo.placements.videotitle, dbo.hits.[getDate()]
FROM         dbo.categories INNER JOIN
                      dbo.placements ON dbo.categories.id = dbo.placements.cid INNER JOIN
                      dbo.publications ON dbo.placements.pid = dbo.publications.id INNER JOIN
                      dbo.type ON dbo.placements.tid = dbo.type.id INNER JOIN
                      dbo.hits ON dbo.placements.id = dbo.hits.placement_id
GROUP BY dbo.placements.id, dbo.placements.eventdate, dbo.placements.product, dbo.placements.filename, dbo.placements.fileurl, dbo.placements.videolink,
                      dbo.placements.videotitle, dbo.hits.[getDate()]
0
 

Author Comment

by:vonfranzken
ID: 16877881
Hmmm back where I started,

See I want the query to show hits meaning I only want each product listed once, with the sum of hits as well as the other field data.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16877890
yeah, i understood, but there will be some columns having  more than one value for a particular product? I need the sample output you are getting now in order to proceed futher
0
 

Author Comment

by:vonfranzken
ID: 16877904
Oh sure

      153      4/1/2006      Larry Italia interview      1            /mediareport/pdfs/04_01_06_TechnologiesWorship_LarryItaliaInterview.pdf                  6/9/2006 4:32:00 AM
      153      4/1/2006      Larry Italia interview      1            /mediareport/pdfs/04_01_06_TechnologiesWorship_LarryItaliaInterview.pdf                  6/9/2006 4:37:32 AM
      153      4/1/2006      Larry Italia interview      1            /mediareport/pdfs/04_01_06_TechnologiesWorship_LarryItaliaInterview.pdf                  6/9/2006 4:37:36 AM
      234      4/1/2006      Podcasts      1            /mediareport/pdfs/04-01-2006_AmericanMusicTeacher_Podcasts.pdf                  6/9/2006 4:37:48 AM
      234      4/1/2006      Podcasts      1            /mediareport/pdfs/04-01-2006_AmericanMusicTeacher_Podcasts.pdf                  6/9/2006 4:38:18 AM
      338      3/1/2006      Real Rhythm      1            /mediareport/pdfs/03-01-06-Parents-RealRhythm.pdf                  6/9/2006 4:38:27 AM
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16877919
SELECT   DISTINCT  TOP 50   dbo.placements.id AS Expr3, dbo.placements.eventdate, dbo.placements.product, SUM(dbo.hits.hit) AS Impressions, dbo.placements.filename,
                      dbo.placements.fileurl, dbo.placements.videolink, dbo.placements.videotitle, MAX(dbo.hits.[getDate()] ) [getDate()]

FROM         dbo.categories INNER JOIN
                      dbo.placements ON dbo.categories.id = dbo.placements.cid INNER JOIN
                      dbo.publications ON dbo.placements.pid = dbo.publications.id INNER JOIN
                      dbo.type ON dbo.placements.tid = dbo.type.id INNER JOIN
                      dbo.hits ON dbo.placements.id = dbo.hits.placement_id
GROUP BY dbo.placements.id, dbo.placements.eventdate, dbo.placements.product, dbo.placements.filename, dbo.placements.fileurl, dbo.placements.videolink,
                      dbo.placements.videotitle, dbo.hits.[getDate()]
0
 

Author Comment

by:vonfranzken
ID: 16877930
     147      3/21/2006      Red Piano - Triple Crown of Polo      1            /mediareport/pdfs/03_21_06_BradentonHerald_TCPRedPiano.pdf                  6/9/2006 1:28:52 PM
      153      4/1/2006      Larry Italia interview      1            /mediareport/pdfs/04_01_06_TechnologiesWorship_LarryItaliaInterview.pdf                  6/9/2006 4:32:00 AM
      153      4/1/2006      Larry Italia interview      1            /mediareport/pdfs/04_01_06_TechnologiesWorship_LarryItaliaInterview.pdf                  6/9/2006 4:37:32 AM
      153      4/1/2006      Larry Italia interview      1            /mediareport/pdfs/04_01_06_TechnologiesWorship_LarryItaliaInterview.pdf                  6/9/2006 4:37:36 AM
      153      4/1/2006      Larry Italia interview      1            /mediareport/pdfs/04_01_06_TechnologiesWorship_LarryItaliaInterview.pdf                  6/9/2006 9:03:50 AM
      234      4/1/2006      Podcasts      1            /mediareport/pdfs/04-01-2006_AmericanMusicTeacher_Podcasts.pdf                  6/9/2006 4:37:48 AM
      234      4/1/2006      Podcasts      1            /mediareport/pdfs/04-01-2006_AmericanMusicTeacher_Podcasts.pdf                  6/9/2006 4:38:18 AM
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16877936
SELECT   TOP 50   dbo.placements.id AS Expr3, dbo.placements.eventdate, dbo.placements.product, SUM(dbo.hits.hit) AS Impressions, dbo.placements.filename,
                      dbo.placements.fileurl, dbo.placements.videolink, dbo.placements.videotitle, MAX(dbo.hits.[getDate()] ) [getDate()]
FROM         dbo.categories INNER JOIN
                      dbo.placements ON dbo.categories.id = dbo.placements.cid INNER JOIN
                      dbo.publications ON dbo.placements.pid = dbo.publications.id INNER JOIN
                      dbo.type ON dbo.placements.tid = dbo.type.id INNER JOIN
                      dbo.hits ON dbo.placements.id = dbo.hits.placement_id
GROUP BY dbo.placements.id, dbo.placements.eventdate, dbo.placements.product, dbo.placements.filename, dbo.placements.fileurl, dbo.placements.videolink
0
 

Author Comment

by:vonfranzken
ID: 16877973
dbo.placements.videotitle is invalid because it is not contained in either the aggregate function or the group by clause

hey thanks for your help with this
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 16877981
put that missing column in the group by list

SELECT   TOP 50   dbo.placements.id AS Expr3, dbo.placements.eventdate, dbo.placements.product, SUM(dbo.hits.hit) AS Impressions, dbo.placements.filename,
                      dbo.placements.fileurl, dbo.placements.videolink, dbo.placements.videotitle, MAX(dbo.hits.[getDate()] ) [getDate()]
FROM         dbo.categories INNER JOIN
                      dbo.placements ON dbo.categories.id = dbo.placements.cid INNER JOIN
                      dbo.publications ON dbo.placements.pid = dbo.publications.id INNER JOIN
                      dbo.type ON dbo.placements.tid = dbo.type.id INNER JOIN
                      dbo.hits ON dbo.placements.id = dbo.hits.placement_id
GROUP BY dbo.placements.id, dbo.placements.eventdate, dbo.placements.product, dbo.placements.filename, dbo.placements.fileurl, dbo.placements.videolink,dbo.placements.videotitle
0
 

Author Comment

by:vonfranzken
ID: 16878000
Yes nice thats it!

What are yo doing when you say to MAX(dbo.hits.[getDate()] ) [getDate()]
and why is get date twice?

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16878013
Usually the 'DISTINCT' clause gives the distinct records, now since the records differ by the [GetDate()] column, we have  to select one of these dates, that's why i used Max([getDate()]), Now your column name is [GetDate()], i 've put the second occurrence, otherwise Max(ColumnName) will give the column in the result set as blank...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now