CODEWITHSUNDEEP
sqlsql-server
George Landry
George Landry

Reputation: 11

'No Column Name' when using Count function in SQL

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

Upvotes: 0

Views: 7221

Answers (2)

Paul Williams
Paul Williams

Reputation: 17040

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
INNER JOIN store s
    ON r.regionid = s.regionid
GROUP BY r.regionid, r.regionname;

Upvotes: 0

dwir182
dwir182

Reputation: 1549

Please do not use Implicit Join, instead use explicit join. 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.

Upvotes: 7

Related Questions