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

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

count similar rows of particular column

1 solution

Solution 1

Add your solution here

Preview 0

count similar rows of particular column

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us