• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

How do I group the filename coloumn so it is only showing 1 result row per filename?

I need to group the distinct filenames. Currently there is a record for every instance of a filename.
select x.*, (SELECT ratingsum / filenamecount) as divnumber
from (
SELECT  filename, rating,
                          (SELECT     SUM(rating) AS Expr1
                            FROM        mytable AS GV
                            WHERE      GV.filename = a.filename) AS ratingsum,
                          (SELECT     COUNT(*) AS Expr2
                            FROM          mytable AS GV
                            WHERE       GV.filename = a.filename) AS filenamecount
                          
FROM         mytable AS a
) x 
ORDER BY divnumber DESC

Open in new window

0
m2ew
Asked:
m2ew
1 Solution
 
pateljituCommented:
Group by clause on filename will do the job.

But in this case there is 'rating' is your select statement, if ratings varies you would still get each rows with different 'rating' and same filename. If 'rating' is not required removing from select statement will work.
select x.filename, (SELECT ratingsum / filenamecount) as divnumber
from (
SELECT  filename, rating,
                          (SELECT     SUM(rating) AS Expr1
                            FROM        mytable AS GV
                            WHERE      GV.filename = a.filename) AS ratingsum,
                          (SELECT     COUNT(*) AS Expr2
                            FROM          mytable AS GV
                            WHERE       GV.filename = a.filename) AS filenamecount
                          
FROM         mytable AS a
) x 
group by filename
ORDER BY divnumber DESC

Open in new window

0
 
SharathData EngineerCommented:
You can simply do like this.
select filename,sum(rating)*1.0/ count(*) divnumber
  from mytable 
 group by filename
 order by divnumber desc

Open in new window

In fact, you can use avg function.
select filename,avg(rating) divnumber
  from mytable 
 group by filename
 order by divnumber desc

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now