Solved

Only want to see Products once in View

Posted on 2006-06-10
17
199 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

948 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

21 Experts available now in Live!

Get 1:1 Help Now