| Author |
Topic |
|
rv498
Yak Posting Veteran
60 Posts |
 Posted - 2014-03-10 : 15:17:33
|
| Find the total children and total cars owned for customers who own homes. SELECT COUNT(NumberChildrenAtHome) AS TotalChildren, COUNT(NumberCarsOwned) AS TotalCars FROM dbo.DimCustomer WHERE HouseOwnerFlag = 1 This query doesn't return correct number of rows. I believe I need derived field. How can I do that? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-10 : 15:19:30
|
| Use SUM function instead of COUNT function. COUNT simply counts the number of rows. To find the number of children you need to ADD UP the number of children - SUM function will do that for you. |
 |
|
|
rv498
Yak Posting Veteran
60 Posts |
Posted - 2014-03-10 : 15:28:32
|
| Thanks but if I follow your logic, I'm partially correct.SELECT SUM(NumberChildrenAtHome), SUM(NumberCarsOwned)FROM dbo.DimCustomerWHERE HouseOwnerFlag = 1when I execute this, I get correct number for total number of cars but incorrect total number of children.How do I use derived field in this? It seems derived field is the weekly topic. |
|
|
|
rv498
Yak Posting Veteran
60 Posts |
Posted - 2014-03-10 : 17:37:31
|
| I came up with this but this is not right. How do I do 2 subqueries under one SELECT?SELECT *FROM( SELECT NumberChildrenAtHome, NumberCarsOwned (SELECT SUM(NumberChildrenAtHome) FROM dbo.DimCustomer WHERE HouseOwnerFlag = 1) AS TotalChildren (SELECT SUM(NumberCarsOwned) FROM dbo.DimCustomer WHERE HouseOwnerFlag = 1) AS TotalCar) ) |
|
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-03-10 : 18:15:54
|
For the case you presented, it doesn't matter if you split the query into subqueries. You would get same result as the solution proposed by James.So far, you got the number of cars correct, but I'm wondering if you are looking for the sum of children REGARDLESS of HouseOwnerFlag=1 (I am of course guessing here)???If so, try this:select sum(NumberChildrenAtHome) as TotalChildren ,sum(case when HouseOwnerFlag=1 then NumberCarsOwned else 0 end) as TotalCars from dbo.DimCustomer |
|
|
|
rv498
Yak Posting Veteran
60 Posts |
Posted - 2014-03-10 : 19:57:04
|
| bitsmed, thanks but your solution still doesn't produce correct total number of children. 18559 is what I got and I need to have 25648. Somebody said you can't do two aggregate functions in one select statement and that's why I did two subqueries. This week I'm learning derived field. Perhaps that needs to be used? |
|
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-03-10 : 20:14:31
|
quote:
Originally posted by rv498
bitsmed, thanks but your solution still doesn't produce correct total number of children. 18559 is what I got and I need to have 25648. Somebody said you can't do two aggregate functions in one select statement and that's why I did two subqueries. This week I'm learning derived field. Perhaps that needs to be used?
I use several aggregate functions in one query all the time, no problem.Are you showing us the whole query or just part of the query?If my last query gave you the result of 18559, then the following query should also give you 18559:select sum(NumberChildrenAtHome) as TotalChildren from dbo.DimCustomer If so, then you will not be able to get the result 25648. At least not with the information you provided, as this last query sum's all records in the table. I assume the field NumberChildrenAtHome are all positive numbers? |
|
|
|
rv498
Yak Posting Veteran
60 Posts |
Posted - 2014-03-10 : 20:28:55
|
| Nm, my mistake. I used a wrong field. Below worked for me. Thanks. SELECT SUM(TotalChildren) AS TotalChildren, SUM(NumberCarsOwned) AS CarsOwned FROM dbo.DimCustomer WHERE HouseOwnerFlag = 1 |
|
|
|
|
Do I need a join here or subquery or derived field