GROUP BY with COUNT select value

Question

1.00/5 (1 vote)

See more:

This is my query

SQL

SELECT
          prod.dep_ID,
          dep.dep_Name,
          cat.cat_Description,
          prod.prod_Description
      FROM
        tblProduct AS prod, appCategory AS cat, tblDepartment AS dep
        WHERE prod.dep_ID = @dep_ID
        AND prod.dep_ID = dep.dep_ID

Now i want to count the total products of each cat_Description.
How can i do that?

Posted 30-Mar-15 0:22am

dannyvkempen

Add a Solution

Comments

Tomas Takac 30-Mar-15 7:24am

What database is this? SQL Server? Do not use the old join syntax. It seems you have a cross join there prod x cat.

1 solution

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)

Maciej Los · Accepted Answer · 2015-03-30T01:21:00

Solution 1

Best guess:

SQL

SELECT prod.dep_ID, dep.dep_Name, cat.cat_Description, COUNT(prod.prod_Description) AS CountOfProductsInCategory
FROM tblProduct AS prod INNER|LEFT|RIGHT JOIN appCategory AS cat ON ...
INNER|LEFT|RIGHT JOIN tblDepartment AS dep ON prod.dep_ID = dep.dep_ID
WHERE prod.dep_ID = @dep_ID
GROUP BY prod.dep_ID, dep.dep_Name, cat.cat_Description

Note: i'm using joins to join data into single result set. There are few ways to join data. Please read this to find out which one is the best for you: Visual Representation of SQL Joins[^].

Posted 30-Mar-15 1:21am

Maciej Los

Comments

dannyvkempen 30-Mar-15 8:35am

Thx for the reply. When i run this query i get an error. dep.dep_Name, cat.cat_Description cannot be found. I get a red line between INNER|LEFT on the | character. The column names are 100% correct

Maciej Los 30-Mar-15 8:42am

You didn't read my answer carefully. As i mentioned there are few ways to join data: LEFT JOIN (get all data from left) or RIGHT JOIN (get all data from right) or INNER JOIN (get equal data on both sides). Choose the best one for you. Please, follow the link to find out.

dannyvkempen 30-Mar-15 8:50am

I see, but when i run the query both on inner joins i get an erron on the second inner. I have this query now:

SELECT prod.dep_ID, dep.dep_Name, cat.cat_Description, COUNT(prod.prod_Description) AS CountOfProductsInCategory
FROM tblProduct AS prod
INNER JOIN appCategory AS cat ON
INNER JOIN tblDepartment AS dep
ON prod.dep_ID = dep.dep_ID
WHERE prod.dep_ID = @dep_ID
GROUP BY prod.dep_ID, dep.dep_Name, cat.cat_Description

Maciej Los 30-Mar-15 8:56am

I don't know relationship between tables. You need to define it using ON table1.Field1 = table2.Field2 statement.
In your case it should be:
INNER JOIN appCategory AS cat ON prod.FieldName = cat.FieldName

dannyvkempen 30-Mar-15 9:21am

The relations as mentioned are correct prod.dep_ID = dep.dep_ID. I still get an error on my second inner join.

Maciej Los 30-Mar-15 9:28am

As i mentioned, you have to define ralationship between cat and prod:

INNER JOIN appCategory AS cat ON (here!!!)
INNER tblDepartment AS dep