[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Returning 0 on Count()

Posted on 2011-10-17
14
Medium Priority
?
206 Views
Last Modified: 2012-05-12
Hi All,

I know I have had a similar question previously but no matter what I try I still haven't been able to crack it...

I currently have the attached syntax which returns the count of records grouped by Country and fuel type:
Country               FuelType           total
Australia             Diesel                      3
Australia             Unleaded Petrol      4
Australia             LPG                      10
Canada                 Unleaded Petrol      5
England/Wales         Diesel                      3
England/Wales         Unleaded Petrol      5
England/Wales          LPG                     12

You will see that there are 3 fuel types. What I need to do is return a count of 0 for all fuel types for all countries so for example, Canada should actually be:

Canada                 Diesel                      0
Canada                 Unleaded Petrol      5
Canada                    LPG                      0

Can anyone suggest a syntax change that would return 0 where applicable?

Thanks,

Rit
SELECT     
 T2.Country, 
 T1.FuelType, 
 ISNULL(COUNT(*), 0) AS total
			
FROM
 tblCars AS T3 INNER JOIN
 tblCountry AS T2 ON T3.CountryID = T2.CountryID RIGHT OUTER JOIN
 tblFuelType AS T1 ON T3.FuelTypeID = T1.FuelTypeID

WHERE     
 T1.FuelTypeID IN (2, 3, 4)

GROUP BY 
 T1.FuelTypeID, 
 T2.Country
 
ORDER BY 
 T2.Country, 
 T1.FuelTypeID

Open in new window

0
Comment
Question by:rito1
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 10

Expert Comment

by:Umar Topia
ID: 36978803
SELECT    
 T2.Country,
 T1.FuelType,
 ISNULL(COUNT(*), 0) AS total
                  
FROM
 tblCars AS T3 INNER JOIN
 tblCountry AS T2 ON T3.CountryID = T2.CountryID RIGHT OUTER JOIN
 tblFuelType AS T1 ON T3.FuelTypeID = T1.FuelTypeID

WHERE    
 T1.FuelTypeID IN (2, 3, 4)

GROUP BY
 T2.Country,
 T1.FuelTypeID

 
ORDER BY
 T2.Country,
 T1.FuelTypeID
0
 
LVL 1

Author Comment

by:rito1
ID: 36978816
Hi umartopia,

The only difference I see is that you have switched the T2.Country and T1.FuelTypeID around within the GROUP BY... this hasn't made a difference i'm afraid.

Rit
0
 
LVL 3

Accepted Solution

by:
clamps earned 400 total points
ID: 36978884
You have to do a sub select since it doesn't work the way you try it...

If you can't think of how that is done, I will help you with the query you need (but I would have to create the tables with some example values first).
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 400 total points
ID: 36978907
> What I need to do is return a count of 0 for all fuel types for all countries

To generate 1 record for each country + fuel type, I think you'd need a CROSS JOIN.  Note: They operate by producing a cartesian product (LOTS of records). So don't use it on large tables).  Then an OUTER join to the cars table. Assuming that table's primary key is named "CarID":

SELECT   cn.Country, ft.FuelType, ISNULL(COUNT(car.CarID), 0) AS total
FROM      tblCountry AS cn 
                     CROSS JOIN tblFuelType ft 
                     LEFT JOIN tblCars as car ON car.CountryID = cn.CountryID
                             AND car.FuelTypeID = ft.FuelTypeID
WHERE     ft.FuelTypeID IN (2, 3, 4)
GROUP BY cn.Country, ft.FuelType
ORDER BY cn.Country, ft.FuelType

Open in new window





0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 400 total points
ID: 36978914
Hi,


try this.

- Bhavesh
SELECT     
 B.Country, 
 A.FuelType, 
 ISNULL(COUNT(B.*), 0) AS total
FROM
(SELECT FT.FuelType 
 FROM tblFuelType FT INNER JOIN tblCars TC 
 ON FT.FuelTypeID = TC.FuelTypeID
 AND FT.FuelTypeID IN (2, 3, 4)
 ) A LEFT OUTER JOIN tblCountry B
 ON A.CountryID = B.CountryID
 
GROUP BY 
 A.FuelTypeID, B.Country
 
ORDER BY 
 B.Country, A.FuelTypeID

Open in new window

0
 
LVL 3

Expert Comment

by:clamps
ID: 36978985
so here's your query...

SELECT T3.Country, T3.FuelType, COUNT(T6.FuelType) AS total
FROM tblCars AS T4
INNER JOIN tblCountry AS T5 ON T4.CountryID=T5.CountryID
INNER JOIN tblFuelType AS T6 ON T4.FuelTypeID=T6.FuelTypeID
RIGHT OUTER JOIN 
(SELECT T1.CountryID, T2.FuelTypeID, Country, FuelType
FROM tblCountry AS T1
CROSS JOIN tblFuelType AS T2
WHERE FuelTypeID IN (2,3,4)) AS T3 ON T3.CountryID=T4.CountryID AND T3.FuelTypeID=T4.FuelTypeID
GROUP BY T3.Country, T3.FuelType
ORDER BY T3.Country, T3.FuelType

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 36979010
Do you mean this result ?
;with meta_data as
(
	SELECT T2.Country, T1.FuelType, ISNULL(COUNT(*), 0) AS total
	FROM tblCars AS T3 
		INNER JOIN tblCountry AS T2 ON T3.CountryID = T2.CountryID 
		RIGHT OUTER JOIN tblFuelType AS T1 ON T3.FuelTypeID = T1.FuelTypeID
	WHERE T1.FuelTypeID IN (2, 3, 4)
	GROUP BY T1.FuelTypeID, T2.Country
)
, country_cte as
(
	select distinct country from meta_data
),
fuel_cte as
(
	select distinct fueltype from meta_data
),
country_fuel_cte as
(
	select * from country_cte, fuel_cte
)
select cte.*, isnull(t.total, 0) total 
from country_fuel_cte cte
	left join meta_data t on cte.country = t.country and cte.FuelType = t.FuelType
order by cte.country

Open in new window


Raj
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36979015
       > To generate 1 record for each country + fuel type

Hm... on second read I'm not so sure that's what you're asking.  Do you want counts for ALL fuel types and countries OR only the ones with at least 1 matching car record?
0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 400 total points
ID: 36979021
select T1.Country, T2.FuelType, 
 (SELECT COUNT(0) FROM tblCars T3 WHERE T3.CountryId = T1.CountryId AND T3.FuelTypeId = T2.FuelTypeId) AS TheCount


 from tblCountry T1 Cross Join tblfueltype T2
 WHERE     
 T2.FuelTypeID IN (2, 3, 4)
 ORDER BY T1.country,T2.fueltype

Open in new window

0
 
LVL 18

Expert Comment

by:deighton
ID: 36979032
my query is to give counts for all countries and fuel types 2,3,4 - including where there is not a matching car (count = 0)
0
 
LVL 3

Expert Comment

by:clamps
ID: 36979061
My query returns all Countries multiplied by all Fueltypes and THEN adds the counts for cars, so you get a country/fueltype combination no matter if there exists a single car with that combination or not and the apropriate counts you asked for...
cheers
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 400 total points
ID: 36979093
This is the shortest version of my query - http:#36979010 (Posted before)
select a.Country, a.FuelType, count(c.CountryID) total from
(
select *
	from tblCountry c cross join tblFuelType f
) a
left join tblCars c on a.CountryID = c.CountryID and a.FuelTypeID = c.FuelTypeID
group by a.Country, a.FuelType
order by a.Country, a.FuelType

Open in new window

0
 
LVL 1

Author Comment

by:rito1
ID: 36979129
Excellent, thank you all for your responses... This is what makes this resource great... generous masterminds :-)

I am just digesting your solutions.
0
 
LVL 1

Author Closing Comment

by:rito1
ID: 36979620
Thanks all, my final syntax is a combination of CROSS JOIN, and sub queries.

Once I have generalised by syntax from my client's schema, I will share it with you.

Again, thanks.

Rit
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question