SQL COUNT function query as per same column value

Question

Am kind of new to sql and I have just come with a situation as shown below. the query that i have so far outputs wrong results i.e

$query = "SELECT sch_name, dist_name COUNT(sch_name) AS total_sch FROM school ORDER BY dist_name";

school

  **sch_name**   **dist_name**
    kaoma        lusaka
    kaloma       lusaka
    momba        mansa
    kebwi        mansa
    matero       ndola

EXPECTED OUTPUT

   **dist_name**     **total_sch**
     lusaka            2
     mansa             2
     ndola             1

At a guess, is this mysql? If so (and even if not) please edit your question and add a suitable tag for your RDBMS product. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 14, 2014 at 7:16

Jithin Shaji · Accepted Answer · 2014-08-14 07:16:42Z

1

Select dist_name,Count(*) total_sch
From School
Group By dist_name
Order By Count(*)

answered Aug 14, 2014 at 7:16

Jithin Shaji

6,1015 gold badges27 silver badges47 bronze badges

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

3 Comments

Ceeee Over a year ago

or SELECT dist_name, COUNT(dist_name) as total_sch FROM school GROUP BY dist_name, COUNT(dist_name) ORDER BY COUNT(dist_name)

mountain Over a year ago

thanks buddy <Jithin Shaji> how can i contact u, for more lessons?

Jithin Shaji Over a year ago

This is a great place to learn,technet.microsoft.com/en-US/sqlserver/ff898410

simo.3792 · Accepted Answer · 2014-08-14 07:28:17Z

1

You need a GROUP BY:

SELECT dist_name, COUNT(sch_name) total_sch
FROM school
GROUP BY dist_name
ORDER BY dist_name

edited Aug 14, 2014 at 7:28

simo.3792

2,2331 gold badge19 silver badges29 bronze badges

answered Aug 14, 2014 at 7:16

MarcinJuraszek

126k15 gold badges199 silver badges267 bronze badges

Collectives™ on Stack Overflow

SQL COUNT function query as per same column value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related