Need to rewrite a SQL query to include counts of a particular column.

Hi,

I am currently using the following query for a search box on my site. I want to add another count() to the returned output that tells me how many rows of each MediaTypeID are returned for a given search.  I'm basicly allowing the user to sort by MediaTypeID and want to display a count for each possible MediaTypeID. The possible values are 1,2,3,4,5.

Im using stored procedures so there can be multiple queries. I just need it done in one call (output).

I'm not a SQL guru and am not sure if I need to do a group by or union type of query.

The returned rows currently look like this:

MediaTypeID     MediaTitle    Hits    DateAdded     CommentCount
----------------------------------------------------------------------------------
1      Dog And Kid Cant Stay Awake      14371      2011-03-17      18
1      Guilty Dog Ate The Cat Treats      31913      2011-03-17      65
1      Girl Pulled On Sled Owned By Dog      12263      2011-02-16      33
4      Dog Photobombs a Picnic      4598      2011-02-09      7
2      Fell Asleep Eating Crackers      3166      5
4      Beware of Dog      3970      2011-01-05      1
1      Useful Dog Tricks      18289      2010-12-28      36
4      Dog Loves Fags      3361      2010-12-17      1
5      Piggy Back Ride      6075      2010-11-06      1
5      3 dogs      2880      2010-11-05      0
3      Dog Pranks Owner      19469      2010-10-29      7
3      Go Bwaaah!      22069      2010-10-25      24

Thanks for any help you can lend me!
SELECT A.MediaTypeID, A.MediaTitle, A.Hits, A.DateAdded,
(SELECT COUNT(CommentID) FROM Comments
WHERE MediaID = A.MediaID) 'CommentCount' FROM Media A
WHERE FREETEXT (*, 'dog') ORDER BY A.DateAdded DESC

Open in new window

maddhacker24Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
You are right, try it this way:
SELECT	m.MediaTypes,
	A.MediaTypeID,
	A.MediaTitle,
	A.Hits,
	A.DateAdded,
	c.CommentCount
FROM	Media A
	INNER JOIN (
		SELECT	MediaTypeID,
			COUNT(*) MediaTypes
		FROM	Media
		GROUP BY
			MediaTypeID) m ON a.MediaTypeID = m.MediaTypeID
	LEFT JOIN (
		SELECT	MediaID,
			COUNT(*) CommentCount
		FROM	Comments
		GROUP BY
			MediaID) c ON a.MediaID = c.MediaID
WHERE	FREETEXT (a.*, 'dog')
ORDER BY
	A.DateAdded DESC

Open in new window

0
 
Anthony PerkinsCommented:
Something like this perhaps:
SELECT	m.MediaTypes,
	A.MediaTypeID,
	A.MediaTitle,
	A.Hits,
	A.DateAdded,
	c.CommentCount
FROM	Media A
	INNER JOIN (
		SELECT	COUNT(*) MediaTypes
		FROM	Media
		GROUP BY
			MediaTypeID) m ON a.MediaTypeID = m.MediaTypeID
	LEFT JOIN (
		SELECT	COUNT(*) CommentCount
		FROM	Comments
		GROUP BY
			MediaID) c ON a.MediaID = c.MediaID
WHERE	FREETEXT (*, 'dog')
ORDER BY 
	A.DateAdded DESC

Open in new window

0
 
SharathData EngineerCommented:
How come you have different counts for same MediaTypeID, say example 1. You have counts as 18, 65,33 and 36. It should be same number. right?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Anthony PerkinsCommented:
>>How come you have different counts for same MediaTypeID, say example 1.<<
That is the CommentCount that is derived from the Comments table.
0
 
maddhacker24Author Commented:
Sharath,

The different counts is from the CommentCount Query which is querying the Comments Table and counting comments for each unique row in the Media Table.

SELECT COUNT(CommentID) FROM Comments
0
 
maddhacker24Author Commented:
acperkins,

I'm getting the following errors when using your query.

Msg 207, Level 16, State 1, Line 13
Invalid column name 'MediaTypeID'.

Msg 207, Level 16, State 1, Line 18
Invalid column name 'MediaID'.

Msg 7615, Level 16, State 1, Line 19
A CONTAINS or FREETEXT predicate can only operate on one table or indexed view. Qualify the use of * with a table or indexed view name.
0
 
Anthony PerkinsCommented:
I suspect they want to see something like this:
MediaTypeID	MediaTitle			Hits	DateAdded		CommentCount
4	1		Dog And Kid Cant Stay Awake		14371	2011-03-17	18
4	1		Guilty Dog Ate The Cat Treats	31913	2011-03-17	65
4	1		Girl Pulled On Sled Owned By Dog	12263	2011-02-16	33
3	4		Dog Photobombs a Picnic		4598	2011-02-09	7
1	2		Fell Asleep Eating Crackers		3166			5
3	4		Beware of Dog			3970	2011-01-05	1
4	1		Useful Dog Tricks			18289	2010-12-28	36
3	4		Dog Loves Fags			3361	2010-12-17	1
2	5		Piggy Back Ride			6075	2010-11-06	1
2	5		3 dogs				2880	2010-11-05	0
2	3		Dog Pranks Owner			19469	2010-10-29	7
2	3		Go Bwaaah!			22069	2010-10-25	24

Open in new window

0
 
CoolBurn28Commented:
Not quite sure wat u mean but i use like for test of your query...
not a good code but hope it can help.
--MediaTypeID     MediaTitle,'			Hits		DateAdded		CommentCount
------------------------------------------------------------------------------------
--1,'Dog And Kid Cant Stay Awake'		14371		2011-03-17      18
--1,'Guilty Dog Ate The Cat Treats'		31913		2011-03-17      65
--1,'Girl Pulled On Sled Owned By Dog'  12263		2011-02-16      33
--4,'Dog Photobombs a Picnic'			4598		2011-02-09      7
--2,'Fell Asleep Eating Crackers'		3166		2011-02-09		5
--4,'Beware of Dog',					3970		2011-01-05      1
--1,'Useful Dog Tricks',				18289		2010-12-28      36
--4,'Dog Loves Fags',					3361		2010-12-17      1
--5,'Piggy Back Ride',					6075		2010-11-06      1
--5,'3 dogs',							2880		2010-11-05      0
--3,'Dog Pranks Owner',					19469		2010-10-29      7
--3,'Go Bwaaah!',						22069		2010-10-25      24

CREATE table Media(MediaTypeID int,MediaTitle nvarchar(50),Hits int,DateAdded datetime);

insert into Media values(1,'Dog And Kid Cant Stay Awake'     ,14371,		2011-03-17);
insert into Media values(2,'Guilty Dog Ate The Cat Treats'   ,31913,		2011-03-17);
insert into Media values(3,'Girl Pulled On Sled Owned By Dog',12263,		2011-02-16);
insert into Media values(4,'Dog Photobombs a Picnic'		   ,4598,		2011-02-09);
insert into Media values(5,'Fell Asleep Eating Crackers'	   ,3166,		2011-02-09);
insert into Media values(6,'Beware of Dog'				   ,3970,		2011-01-05);
insert into Media values(7,'Useful Dog Tricks'			   ,18289,		2010-12-28);
insert into Media values(8,'Dog Loves Fags'				   ,3361,		2010-12-17);
insert into Media values(9,'Piggy Back Ride'				   ,6075,		2010-11-06);
insert into Media values(10,'3 dogs'						   ,2880,		2010-11-05);
insert into Media values(11,'Dog Pranks Owner'			  ,19469,		2010-10-29);
insert into Media values(12,'Go Bwaaah!'					  ,22069,		2010-10-25);

ALTER FULLTEXT INDEX ON Media ADD (MediaTitle)
GO


CREATE table Comments(CommentID int,MediaTypeID int);
insert into Comments values(1,18);
insert into Comments values(1,65);
insert into Comments values(1,33);
insert into Comments values(1,7);
insert into Comments values(1,5);
insert into Comments values(1,1);
insert into Comments values(1,36);
insert into Comments values(1,1);
insert into Comments values(1,1);
insert into Comments values(1,0);
insert into Comments values(1,7);
insert into Comments values(1,24);
--
DECLARE @SearchText nvarchar(50)
set @SearchText = 'dog'
SELECT A.MediaTypeID, 
       A.MediaTitle, 
       A.Hits, 
       A.DateAdded,
       (SELECT COUNT(CommentID) FROM Comments WHERE MediaTypeID = A.MediaTypeID) 'CommentCount' 
FROM  Media A
WHERE MediaTitle LIKE '%'+ @SearchText + '%' 
ORDER BY A.DateAdded DESC

Open in new window

0
 
maddhacker24Author Commented:
acperkins,

Ok, we are getting close. I got the counts for the MediaTypeID but they were for the entire Media table. I need the counts just for anything matching the search term. In this example I need to count the MediaTypes for each row in Media that matches "dog".
0
 
SharathData EngineerCommented:
may be this?
acperkins - I just included the WHERE condition in the sub query also.
SELECT	m.MediaTypes,
	A.MediaTypeID,
	A.MediaTitle,
	A.Hits,
	A.DateAdded,
	c.CommentCount
FROM	Media A
	INNER JOIN (
		SELECT	MediaTypeID,
			COUNT(*) MediaTypes
		FROM	Media
		WHERE	FREETEXT (*, 'dog')
		GROUP BY
			MediaTypeID) m ON a.MediaTypeID = m.MediaTypeID
	LEFT JOIN (
		SELECT	MediaID,
			COUNT(*) CommentCount
		FROM	Comments
		GROUP BY
			MediaID) c ON a.MediaID = c.MediaID
WHERE	FREETEXT (a.*, 'dog')
ORDER BY
	A.DateAdded DESC

Open in new window

0
 
Anthony PerkinsCommented:
>> I just included the WHERE condition in the sub query also.<<
That should work, although might be a tad slow.  Perhaps a CTE or using a temp table would be a better approach.
0
 
maddhacker24Author Commented:
Thanks for your help. I instead decided to make 2 calls for performance. It just made more sense.
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.