Solved

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

Posted on 2011-03-23
12
336 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:maddhacker24
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>How come you have different counts for same MediaTypeID, say example 1.<<
That is the CommentCount that is derived from the Comments table.
0
 

Author Comment

by:maddhacker24
Comment Utility
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
 

Author Comment

by:maddhacker24
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
Comment Utility
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
 
LVL 2

Expert Comment

by:CoolBurn28
Comment Utility
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
 

Author Comment

by:maddhacker24
Comment Utility
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
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>> 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
 

Author Closing Comment

by:maddhacker24
Comment Utility
Thanks for your help. I instead decided to make 2 calls for performance. It just made more sense.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

728 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

9 Experts available now in Live!

Get 1:1 Help Now