CODEWITHSUNDEEP
mysqlsql
Bart g
Bart g

Reputation: 585

Get count on SQL

How can I do it so that when I run this query.

SELECT distinct cus_areacode AS "Area Code", cus_code AS "Number"
FROM CUSTOMER
WHERE cus_areacode = 713 OR cus_areacode = 615;

instead of showing the following.

Area Code   Number
713         10015
713         10018
615         10019
615         10017
713         10011
615         10010
615         10016
615         10012
615         10014
615         10013

It may show this.

 Area Code  Number
    615       7
    713       3

I tried this

SELECT distinct cus_areacode AS "Area Code", count(cus_code) AS "Number"
FROM CUSTOMER
WHERE cus_areacode = 713 OR cus_areacode = 615;

But it does not work.

Upvotes: 0

Views: 121

Answers (6)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

Try this

SELECT cus_areacode AS "Area Code", count(cus_code) AS "Number"
FROM CUSTOMER 
WHERE cus_areacode IN(713,615)
GROUP BY cus_areacode;

OR:

SELECT cus_areacode AS "Area Code", count(cus_code) AS "Number"
FROM CUSTOMER
WHERE cus_areacode = 713 OR cus_areacode = 615
GROUP BY cus_areacode;

Fiddle Demo Here

Upvotes: 2

jmail
jmail

Reputation: 6132

you need this:

First of all I appreciate to tried query, your going right way, And almost got it you should just remove the distinct and add the Group By your area code.

And they distinct mean its In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.

And, The Group by means in a statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

And you code is following as:

SELECT cus_areacode AS "Area Code", count(cus_code) AS "Number"
FROM CUSTOMER
WHERE cus_areacode = 713 OR cus_areacode = 615
GROUP BY cus_areacode;

SQL FIDDLE

Upvotes: 1

Ashish Gaur
Ashish Gaur

Reputation: 2050

SELECT cus_areacode AS "Area Code", count(cus_code) AS "Number" FROM CUSTOMER
WHERE cus_areacode = 713 OR cus_areacode = 615
GROUP BY cus_areacode;

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 1549

try this:

select * from 
(
select cus_areacode , cus_code 
FROM CUSTOMER
WHERE cus_areacode = 713 OR cus_areacode = 615 
) tempalias  
group by cus_areacode

Upvotes: 0

SagarPPanchal
SagarPPanchal

Reputation: 10131

SELECT areacode,count(cus_code) FROM CUSTOMER GROUP BY areacode

Upvotes: 0

Ilesh Patel
Ilesh Patel

Reputation: 2155

SELECT cus_areacode AS "Area Code", count(cus_code) AS "Number"
FROM CUSTOMER GROUP BY cus_areacode

Upvotes: 2

Related Questions