Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-23
12
Medium Priority
?
379 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
ID: 35203637
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 41

Expert Comment

by:Sharath
ID: 35203646
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
ID: 35203661
>>How come you have different counts for same MediaTypeID, say example 1.<<
That is the CommentCount that is derived from the Comments table.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:maddhacker24
ID: 35203669
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
ID: 35203675
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
ID: 35203677
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 35203686
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
ID: 35203822
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
ID: 35203975
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 41

Expert Comment

by:Sharath
ID: 35204798
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
ID: 35206215
>> 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
ID: 35233964
Thanks for your help. I instead decided to make 2 calls for performance. It just made more sense.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

972 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