Nested Selects 1

[michaela18]

I want to learn how to do nested selects, I am confused by the "(" and the commas. How do I combine all these selects into 1 so it shows on 1 result panel?

Select date, Count (*) as '1_year'
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Plan <> ''
and Terms in ('0', '1')
group by date


Select date, Count (*) as '2_year'
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
and PhoneModel <> ''
group by date


Select date, Count (*) as 'US'
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
and PhoneModel = ''
and left(MobilePhoneNumber,3) in ('201', '551', '609', '732', '848', '856', '862', '908', '973', '215', '267', '412', '484', '570', '610', '717', '724', '814')
group by date


Select date, Count (*) as 'AVS'
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
and PhoneModel = ''
and left(MobilePhoneNumber,3) not in ('201', '551', '609', '732', '848', '856', '862', '908', '973', '215', '267', '412', '484', '570', '610', '717', '724', '814')
and IVRComplete = 'Y'
group by date


Select date, Count (*) as 'Total'
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
group by date

 

[bborissov]

UNION, but add one more field that tells you what you select, and put COUNT(*) in other:

Select date, '1_year' AS Notes, Count (*) as Cnt
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Plan <> ''
and Terms in ('0', '1')
group by date

UNION ALL

Select date, '2_year' AS Notes, Count (*) as Cnt
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
and PhoneModel <> ''
group by date
....
UNION ALL
Select date, 'Total' AS Notes, Count (*) as Cnt
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
group by date

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Forget the nested selects (for this query). There is a better way.

With this particular piece of code, you have 5 queries. This can be reduced down to a single query without any nested selects.

Before we get started, we should realize that you are looking for various counts from the same table. A little "refresher" on Count may help. Count(*) will count ALL of the rows in a result set (that satisfy a where clause). If you use Count(ColumnName), you will get a count of rows where ColumnName is not null. Ex: You have 10 rows in a table, and the CanBeNull column has 2 NULLs. Count(*) = 10, Count(CanBeNull) would be 8. Count (and all other aggregates) ignore NULLS. We can use this to our advantage.

Back tot he query... First, identify the part that is common to all queries, which would be this...

Select date[s], Count (*) as 'Total'[/s]
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
group by date

Now, how can we use the "Count ignores NULL" trick? Like this:

[tt]Select Count(Case When SomeCondition = 'SomeValue' Then 1 End)[/tt]

If SomeCondition = 'SomeValue', the value 1 will be counted. Since 1 is not null, you will indeed get a count. When SomeCondition is not 'SomeValue', the Case/When expression will return NULL and that row will NOT be counted.

For example, for a row to be counted as AVS, the PhoneModel must be an empty string, the MobilePhoneNumber must start with a bunch of things and IVRComplete = 'Y'. So....

Select date, 
       Count([red]Case When PhoneModel = ''
                       and left(MobilePhoneNumber,3) not in ('201', '551', '609', '732', '848', '856', '862', '908', '973', '215', '267', '412', '484', '570', '610', '717', '724', '814')
                       and IVRComplete = 'Y'
                  Then 1 END[/red]) As [AVS],
       Count (*) as 'Total'
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
group by date

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[michaela18]

gmmastros (Programmer)
4 Mar 10 13:40
Forget the nested selects (for this query). There is a better way.

With this particular piece of code, you have 5 queries. This can be reduced down to a single query without any nested selects.

Before we get started, we should realize that you are looking for various counts from the same table. A little "refresher" on Count may help. Count(*) will count ALL of the rows in a result set (that satisfy a where clause). If you use Count(ColumnName), you will get a count of rows where ColumnName is not null. Ex: You have 10 rows in a table, and the CanBeNull column has 2 NULLs. Count(*) = 10, Count(CanBeNull) would be 8. Count (and all other aggregates) ignore NULLS. We can use this to our advantage.

Back tot he query... First, identify the part that is common to all queries, which would be this...

CODE
Select date, Count (*) as 'Total'
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
group by date

Now, how can we use the "Count ignores NULL" trick? Like this:

Select Count(Case When SomeCondition = 'SomeValue' Then 1 End)

If SomeCondition = 'SomeValue', the value 1 will be counted. Since 1 is not null, you will indeed get a count. When SomeCondition is not 'SomeValue', the Case/When expression will return NULL and that row will NOT be counted.

For example, for a row to be counted as AVS, the PhoneModel must be an empty string, the MobilePhoneNumber must start with a bunch of things and IVRComplete = 'Y'. So....

CODE
Select date,
Count(Case When PhoneModel = ''
and left(MobilePhoneNumber,3) not in ('201', '551', '609', '732', '848', '856', '862', '908', '973', '215', '267', '412', '484', '570', '610', '717', '724', '814')
and IVRComplete = 'Y'
Then 1 END) As [AVS],
Count (*) as 'Total'
from callrecord
where date >= '20100201'
and date <= '20100224'
and State in ('Process', 'Great')
and Fam= 'N'
and CodeIN ('100', '101', '105', '111', '113', '102','115', '117','120','121', '123', '124')
and Terms = '2'
group by date

Make sense?

This makes a lot of sense and it helped a lot. I really appreciate it. I will try it now

 

[michaela18]

One more question:


Not all the dates has records attached to it, is it possible to still display those dates but with 0 in every row?

O yeah, the query worked perfectly!!