'No Column Name' when using Count function in SQL

1265 views sql
2

I am trying to display the region id, region name, and number of stores in the region for all regions with this query

SELECT r.regionid, r.regionname, COUNT(*),
FROM region r, store s
WHERE r.regionid = s.regionid
GROUP BY r.regionid, r.regionname;

The count column says "(No column name)" and I cannot figure out how to fix that

answered question

Just give it an alias -- select ... count(*) as NumberOfStores from. Please note, no comma after the last field and the from statement. Also, I would suggest using an explicit join over using commas in your from clause.

2 Answers

5

You need to give the count column an alias. For example, to call it "regionCount", use this:

SELECT r.regionid, r.regionname, COUNT(*) as [regionCount],
FROM region r, store s
WHERE r.regionid = s.regionid
GROUP BY r.regionid, r.regionname;

posted this
4

Please do not use Implicit Join use Comma you can read more about why you must not use in Bad habits to kick : using old-style JOINs

SELECT 
   reg.regionid, 
   reg.regionname, 
   COUNT(*) as regionCount
FROM 
   region reg 
   Inner Join store st on reg.regionid = st.regionid
GROUP BY 
   reg.regionid, 
   reg.regionname; 

And i am suggesting not use alias table like r or s why don't use reg for region and st for store.. It will help you when you make complex query become readable..

And for your error it's clear.. You just need to name your count column.

posted this

Have an answer?

JD

Please login first before posting an answer.