July 2, 2012 at 10:50 am
I have a code similiar to this and I want to populate a zero instead of pulling back nothing.
SELECT
'Referral' AS Queue
,a.Field 1
,a.Field 2
,Count(*) AS Volume
FROM Table_Name a
JOIN (SELECT
Field1
,MAX(Field2) AS FieldName
WHERE Field1 = 1
GROUP BY
Field1
) b
ON a.Field1 = b.Field1
WHERE a.Field1 = 1
GROUP BY a.Field1
,a.Field2
July 2, 2012 at 11:14 am
Do you have a separate table with the values you want to count against?
For example, a table of customers and prospects, and a table of orders placed. You want a count of orders for each customer/prospect, and some of them won't have any.
If you have a baseline table like that, then you select from that, and then use an inline subquery to select the count. Like so:
select ID, Name,
(select count(*)
from dbo.OrdersPlaced
where CustomerID = Customers.ID) as QtyOrders
from dbo.Customers ;
Do you have something you can use to get all the combinations you want to count?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2012 at 11:19 am
Thanks!
No, I don't have a baseline table. I'm just filtering through the subquery and where clause to get the
rows that I need and then counting rows.
But in some instances, no rows come back but I need to account for that part of my query still even if
it's a zero.
July 2, 2012 at 11:21 am
How do you know which rows are missing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply