Count Line Item Catagories

Question

0.00/5 (No votes)

See more:

I'm trying to get a sql query working to count the number of items by their categories to display on a webpage. Any assistance would be appreciated

i want to count the Catagories with jobs

like

Sales&Marketing(5)
Accounts and Finance(2)

as so on.. my tables are
CataGories Table

CatID   CatName
1       Sales&Markting
2       Account and Finance
3       Web DEVELOPMENT
4       Media

and other table as Job

JobID  CatID  JOBtitle
  1 2 Sales Manager
  2       2    Seles Executive
  2       3    Java Developer

So plz tell me i m understand to get result

Posted 20-Sep-13 8:26am

paracha1

Updated 20-Sep-13 8:33am

Maciej Los

v3

Add a Solution

3 solutions

Solution 2

Try to use below query:

SQL

SELECT 
FROM (CatName + ' (' + CONVERT(VARCHAR(30),CountOfCtgs) + ')'
    SELECT C.CatName, COUNT(C.CatName) AS CountOfCtgs
    FROM Categories AS C INNER JOIN Job AS J ON C.CatID = J.CatID
    GROUP BY C.CatName
    ORDER BY C.CatName
) AS T

Posted 20-Sep-13 8:36am

Maciej Los

Updated 21-Sep-13 5:52am

v2

Comments

Maciej Los 20-Sep-13 14:43pm

You're welcome ;)

paracha1 21-Sep-13 2:56am

Mr. Maciej Los;
----
SELECT C.CatName, COUNT(C.CatName)
FROM Categories AS C INNER JOIN Job AS J ON C.CatID = J.CatID
GROUP BY C.CatName -------

This query is executed Sucess but show only those record who jobs asigned catagories but other catagoreis will not show this query show error on asp.net page.

i want to show result like as Sales&Markting(2)HR(3)Account and Financ(4)

Information&Technology(0)Medical(0)Engineering(0)

and now tell me how is possibleplz send me the sultion as soon as possible

Thanks Maciej

Maciej Los 21-Sep-13 11:52am

Have a look at the answer now ;)

paracha1 21-Sep-13 13:16pm

i m hardly trying to get result but i m not able to get the result like as
Sales&Markting(2)HR(3)Account and Financ(4)

Information&Technology(0)Medical(0)Engineering(0)
but show only catagories will asigned job other catagories not show.i have 6 job using 4 catagoreis. just tell me plz show empty catagories show HR(0) Management(0) and so on...........plz tell me the right query. thanks u

Maciej Los 22-Sep-13 13:04pm

Replace INNER JOIN with RIGHT JOIN and/or LEFT JOIN and see what happens ;)

paracha1 22-Sep-13 13:29pm

Dear Maciej, thanks to helped

when i change the query inner join to Left join/Right join
the Action of Left Join given the result is :
Asigned 1 of each catagories.
like Medical(1) Engineering (1) and so on
but i have no job regarding these Catagories.
and used Right Join the will noting new show same result as Inner Join
plz help

Maciej Los 22-Sep-13 15:45pm

Right join can't return the same result as Inner join...

paracha1 22-Sep-13 17:52pm

ok thanks i will try to solve the issue u thanks to helped me in many time. you have good experience may God bless u thanks

Solution 3

i hardly trying and solove its problem
thanks to all

Quote:
SELECT c.CatName, COUNT(j.JobID) as Result FROM Job j
right JOIN CATAGORIES c ON j.CatID=c.CatID
GROUP BY C.CatName

Posted 23-Sep-13 22:41pm

paracha1

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)

OriginalGriff · Accepted Answer · 2013-09-20T08:35:00

Solution 1

Try:

SQL

SELECT c.CatName, COUNT(j.JobID) FROM Job j 
JOIN Catagories c ON j.CatID=c.CatID 
GROUP BY C.CatName

Posted 20-Sep-13 8:35am

OriginalGriff

Updated 20-Sep-13 8:44am

v2

Comments

Maciej Los 20-Sep-13 14:38pm

You were a bit quickest, Paul ;)

paracha1 20-Sep-13 14:42pm

no its not working....... its will display the message
Column 'Catagories.CatName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.