Solved

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

Posted on 2011-03-23
12
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

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 125 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

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