Solved

Only want to see Products once in View

Posted on 2006-06-10
17
203 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
[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
  • 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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
 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

732 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