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

x
?
Solved

Only want to see Products once in View

Posted on 2006-06-10
17
Medium Priority
?
209 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 93

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

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

578 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