SQL grouping in MySQL

Question

Re all,

I need a little help with SQL. I got two SQL queries:

the first one counts how many occurences of a certain condition:

SELECT COUNT(*) 
FROM table1
WHERE condition1 = @condition

the second one select each row that match the same condition on the same table joined with a secondary table:

SELECT *
FROM table1 INNER JOIN table2
ON table2.id = table1.id_sub
WHERE condition1 = @condition

Using both these queries in interactive fashion (by code) I can achieve the result I need but I want to optimize the procedure using SQL features instead coding.

What I really need is a query that reports just a single row for each group of rows that matches condition1. I need also to insert the count of the matching rows as a field of the result row.

I'm running MySQL 5.6 and code the application trough Visual Studio 2013 Pro coding in C# (using MySQL.Data package).

Best Regards, WeirdGyn

I've written a possible answer but without further clarification on your table structures and what columns you need to return it's hard to say if these answer will be what you're looking for. — AeroX
– AeroX, Commented Jan 16, 2015 at 17:00

AeroX · Accepted Answer · 2015-01-16 16:58:09Z

1

All you need to do is GROUP BY the condition1 field. It would also appear that if you just need the COUNT by group that you wouldn't need the join.

SELECT condition1, COUNT(*)
FROM table1
--INNER JOIN table2
--    ON table1.id_sub = table2.id
GROUP BY condition1

answered Jan 16, 2015 at 16:58

AeroX

3,4712 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

weirdgyn Over a year ago

Sorry but I forgot to mention that condition1 (in the second query) is based on data from table2 (that's why I need INNER JOIN).

Collectives™ on Stack Overflow

SQL grouping in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related