Only want to see Products once in View

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()]
vonfranzkenAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
are you getting duplicate values
0
 
Patrick MatthewsCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
vonfranzkenAuthor Commented:
Yes duplicate values for product.

not quite patrick
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
if the above is not working can you post the sample output u r getting while running the above
0
 
vonfranzkenAuthor Commented:
Error is SELECT clause: expression near DISTINCT missing from clause
unable to parse query
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
vonfranzkenAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
vonfranzkenAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
vonfranzkenAuthor Commented:
     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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
vonfranzkenAuthor Commented:
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
 
vonfranzkenAuthor Commented:
Yes nice thats it!

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

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.