display total no. of posts in a category on sidebar

Question

0.00/5 (No votes)

See more:

i want to display category list on my sidebar with count of its total posts.
for ex: if there are 40 posts under category sports it should display like --> Sports(40)
pls help guys i'm stuck with this

Posted 9-Jul-13 1:09am

nkhldhar

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-07-09T02:41:00

Hi,

Check the below Code....

SQL

DECLARE @Cat TABLE(CatID INT, CatDesc VARCHAR(200))
DECLARE @TranDtls TABLE (CatID INT, PostCount INT)

INSERT INTO @Cat(CatID, CatDesc)
SELECT 1, 'Sports'
UNION ALL SELECT 2,'Accounts'
UNION ALL SELECT 3,'IT'
UNION ALL SELECT 4,'General'

INSERT INTO @TranDtls (CatID, PostCount )
SELECT 1,10
UNION ALL SELECT 1,1
UNION ALL SELECT 1,3
UNION ALL SELECT 2,1
UNION ALL SELECT 2,5
UNION ALL SELECT 4,16


SELECT C.CatDesc+'('+CAST(ISNULL(T.PostCount ,0) AS VARCHAR(20))+')' AS Category
FROM @Cat C
LEFT OUTER JOIN (SELECT CatID, SUM(PostCount) 'PostCount' FROM @TranDtls GROUP BY CatID) T ON T.CatID=C.CatID 

-- Note : In case you have single entry for each category post, Use COUNT Function.

Regards,
GVPrabu

**Nirav Prabtani** · Accepted Answer · 2013-07-09T01:12:00

Solution 1

SQL

select count(sportID)as SportCount from sportTable

give datasourse to label...:)

C#

label1.Text=Datatable1.Rows[0]["SportCount "].toString();

Posted 9-Jul-13 1:12am

Nirav Prabtani

Comments

Adarsh chauhan 9-Jul-13 7:28am

+5 Nirav.. nice one

Nirav Prabtani 9-Jul-13 7:29am

thanks Adarsh.... :)

gvprabu 9-Jul-13 8:47am

How "select count(sportID)as SportCount from sportTable" this Query will work without GROUP BY?

gvprabu 9-Jul-13 8:31am

Is this Solution is help full to you?

Nirav Prabtani 9-Jul-13 8:33am

is it wrong???

gvprabu 9-Jul-13 8:41am

Check the solution 2...

Nirav Prabtani 9-Jul-13 8:46am

My dear there is no any kind of description about exact need about.
so i suggest solution as count query....:)

gvprabu 9-Jul-13 8:48am

see if he asked like if a category is "Sports" then Count or Sum is "40"... his required output is Sports(40) right...

Nirav Prabtani 9-Jul-13 8:52am

ya he can also put lable instead (40) like...
Sport(<asp:Label>)
I fill label text with 40.......:)

gvprabu 9-Jul-13 8:52am

mmmm...Ok fine... we can do this in DB it self. :-)

Nirav Prabtani 9-Jul-13 8:54am

ya...thanx...:)
but it is easy way to do it...:)

gvprabu 9-Jul-13 8:59am

the problem is later they need "Sports - 40" like format then u have to change the front end screen and re build the application right. So if we do all these changes back end then only SP we have to release thats all.

Nirav Prabtani 9-Jul-13 9:02am

ya but the questioner is beginner so i suggest easiest way by d way your solution is better then mine you have done a good job ........:)

gvprabu 9-Jul-13 9:19am

Its not like that... you know both the side front end and back end... I am working in back end only. like this some changes we ll do in front end then we faced some problem in my previous comp... thats y we will do some of the operations in back end.

Nirav Prabtani 9-Jul-13 9:23am

ya i know you are right thats why i said you have done a good job don't take it wrong,
i appreciated your solution .... :):)

gvprabu 9-Jul-13 9:27am

hi man... I didn't take it as wrong... thanks you so much...

Nirav Prabtani 9-Jul-13 9:29am

welcome... :)