CODEWITHSUNDEEP
sqlsql-server
user125264
user125264

Reputation: 1827

aggregate functions how to count a column and return the value in the same query

I am trying to learn how aggregate functions work in SQL and I cant work out how to calculate the number of times a value appears within a query.

Im using MSSQL 2008 and everything I try seems to cause an error.

Below is my query:

SELECT category, 
       template, 
       galleryshortDescription, 
       galleryLongDescription, 
       GalleryName,   
       GalleryTitle, 
       GalleryID, 
       GalleryCreatedDate, 
       GalleryLastUpdated, 
       GalleryPublished, 
       GalleryViews, 
       ObjectID, 
       GalleryDescription, 
       HtmlMetaKeywords, 
       HtmlMetaDescription

FROM dbo.ImageGallery

I would like to return the category field as well as the total number of times it appears in this query and I have tried using 

count (category) AS category_counter

any suggestions greatly appreciated

thanks in advance

Upvotes: 1

Views: 178

Answers (4)

Aditya Kumar Upadhyay
Aditya Kumar Upadhyay

Reputation: 86

It is better to store category and No_of_category in Temp table. After it you can join with your table to Temp table.. Eg.

SELECT  category, count(category) No_Of_category
into #temp
  FROM dbo.ImageGallery 
     group by category

SELECT cat.category, t.No_Of_category,
      cat. template, 
       cat.galleryshortDescription, 
       cat.galleryLongDescription, 
       cat.GalleryName,   
       cat.GalleryTitle, 
       cat.GalleryID, 
       cat.GalleryCreatedDate, 
       cat.GalleryLastUpdated, 
       cat.GalleryPublished, 
       cat.GalleryViews, 
       cat.ObjectID, 
       cat.GalleryDescription, 
       cat.HtmlMetaKeywords, 
       cat.HtmlMetaDescription

FROM dbo.ImageGallery cat
left outer join #temp t on cat.category=t.category

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

i would like to return the category field as well as the total number of times it appears in this query

What you need, is to use a GROUP BY, with COUNT like so:

SELECT
  Category,
  COUNT(category) AS category_counter
FROM  dbo.ImageGallery
GROUP BY category;

SQL Fiddle Demo

For instance, this query will give you something like:

|  CATEGORY | CATEGORY_COUNTER |
--------------------------------
| Category1 |                2 |
| Category2 |                2 |
| Category3 |                3 |
| Category4 |                3 |

But you have a big problem, in your table.

Your table this way isn't normalized, you should split this table into the following tables:

Categories:

  • CategoryId,
  • CategoryName.

GalleriesProperties:

  • GalleryId,
  • GalleryName,
  • GalleryshortDescription,
  • GalleryLongDescription,
  • GalleryTitle,
  • GalleryCreatedDate,
  • GalleryLastUpdated,
  • GalleryPublished,
  • GalleryViews,
  • GalleryDescription.

HTMLMetas

  • HTMLMetaID
  • HtmlMetaKeywords,
  • HtmlMetaDescription

Then your table ImageGallery would be something like:

  • GalleryId,
  • CategoryId a foreign key references Categories table(CategoryID),
  • Template,
  • HTMLMetaID a foreign key to the htmlmeta table.

This is just an example, it might needs more tweaks in your context. But you should read more about this.

Upvotes: 1

syed mohsin
syed mohsin

Reputation: 2938

You have to use Count(category) and group by category like this 

SELECT  category, count(category) as ColumnNameofYourChoice
  FROM dbo.ImageGallery 
     group by category

When using count you have to use group by, on which, you want the count to happen.

Upvotes: 0

stearm
stearm

Reputation: 143

You have to use GROUP BY and COUNT. Let's try to read: http://www.w3schools.com/sql/sql_func_count.asp and http://www.w3schools.com/sql/sql_groupby.asp

Upvotes: 0

Related Questions