Click here to Skip to main content
15,561,685 members
Please Sign up or sign in to vote.
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

1 solution

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
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900