count similar rows of particular column

Question

0.00/5 (No votes)

See more:

Hi,

I have written a My SQL query that involves joining of another table and have done group by type and uid and result is something like shown below -

Type    UID     Count
MQ	37061	2
MQ	37083	1
MQ	45914	6
MQ	46520	1
MQ	47958	1
MQ	48189	1
MQ	49731	3
MQ	59893	3
MQ	68806	1
MQ	71396	1
MQ	73186	1
MQ	73770	1
NS	1057	1
NS	10717	2
NS	16310	1
NS	16512	1
NS	31885	2
NS	39441	2
NS	40578	3
NS	46166	3
NS	68353	1
NS	71169	2
NS	72483	1

it results - type, uid and number of times same user id appears for that type.

Now, I want to know the number of times same type appears. There are thousands of rows.
In other words, in above result I want to count total number of MQ and NS in the list.

Any idea how to achieve that?

Thanks a lot

Posted 6-Sep-13 6:14am

AndyInUK

Add a Solution

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)

**Zoltán Zörgő** · Answer 1 · 2013-09-06T06:18:00

Solution 1

You can simply use nested query. Let's assume your inner query is what you have made so far, and it is returning type, uid and count fields.

SQL

select type, count(*) from 
(/*...your inner query comes here...*/) 
group by type

Or, if you want to summarize the counted values, use:

SQL

select type, sum("count") from 
(/*...your inner query comes here...*/) 
group by type

Posted 6-Sep-13 6:18am

Zoltán Zörgő

Updated 6-Sep-13 6:20am

v2

Comments

AndyInUK 6-Sep-13 12:32pm

As per your suggestion, i tried below query but am getting error - 'Every derived table must have its own alias' and with sum(*) am getting some other errors in the query.

SELECT member.type, COUNT(*) FROM (
SELECT
member.type,
v.id,
COUNT(v.path)AS `count`
FROM
views v
INNER JOIN member ON v.id = member.uid
WHERE
v.path = "/asd/des/gef/index.php"
GROUP BY
member.type,
v.id
) group by member.type

I have also tried WITH ROLLUP, but that just sums the count and not the type.

Zoltán Zörgő 6-Sep-13 14:18pm

Try:


SELECT T.type, COUNT(T.*) FROM (
			SELECT
			member.type,
			v.id,
			COUNT(v.path)AS `count`
			FROM
			views v 
			INNER JOIN member ON v.id = member.uid
			WHERE
			v.path = "/asd/des/gef/index.php"
			GROUP BY
			member.type,
			v.id
) AS T 
group by T.type

Note the alias