Multiple Count Distincts

Dev Shed Forums Sponsor:

Dell PowerEdge Servers

December 20th, 2003, 06:20 AM

roctech

Junior Member

Join Date: Oct 2003

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Multiple Count Distincts

Hi All,

I'm fairly new to MSSQL, and I have a problem. I need to run a query on a table to make distinct counts on how many items do not meet criteria, and also make distinct counts all items regardless if they meet criteria or not, and output it all to an HTML table with four columns. I use PHP on the frontend for this, and I have been able to run both queries and output the results, however the "Total" numbers do not match up with the "Nodes" column.

This is the query that counts the distinct items that do not meet criteria:

Code:

 SELECT Node, City, count (distinct Address) as Problems
FROM  addy
join SignalQuality
on SignalQuality.Address = addy.mac
where (SignalQuality.TxPower not between 35 and 55 or SignalQuality.RxPower not between -10 and 11 or SignalQuality.RxSNR < 32 or SignalQuality.TSRxPower not between -2 and 2 or SignalQuality.TSRxSNR < 25) and (SignalQuality.Address = addy.mac and Corp = '$Corp')
group by addy.node, addy.city
order by City, Problems DESC

This is the query that would count all distinct items:

Code:

 SELECT Node, City, count (distinct Address) as Totals
FROM  addy
join SignalQuality
on SignalQuality.Address = addy.mac
where (SignalQuality.Address = addy.mac and Corp = '$Corp')
group by addy.node, addy.city
order by City, Problems DESC

I want it to output like this:

City Node Problems Total
Anytown C10 45 115

Any help greatly appreciated!

December 20th, 2003, 07:49 AM

r937

SQL Consultant

Dev Shed God 24th Plane (16500 - 16999 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,743

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 18 m 16 sec
Reputation Power: 870

first of all, a comment on your criteria for Problems

your use of OR will ensure every row is a problem

if you have WHERE X NOT BETWEEN 35 and 55 OR X NOT BETWEEN -10 AND 11, then no matter what value you care to consider, it's going to be NOT between one range OR the other

you could either change all the ORs to ANDs or reverse them by removing the NOTs

here's your combined query:

Code:

select addy.node
     , addy.city
     , sum(
        case when signalquality.txpower between 35 and 55 
               or signalquality.rxpower between -10 and 11 
               or signalquality.rxsnr >= 32 
               or signalquality.tsrxpower between -2 and 2 
               or signalquality.tsrxsnr >= 25
             then 1
             else 0 end )      as Problems     
     , count(distinct address) as Totals
  from addy
inner
  join signalquality
    on addy.mac = signalquality.address
 where corp = '$corp'
group 
    by addy.node
     , addy.city
order 
    by addy.city
     , Problems desc

rudy
http://r937.com/

December 20th, 2003, 04:17 PM

roctech

Junior Member

Join Date: Oct 2003

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Rudy,

Thanks for replying. I think you've got me going in the right direction, but it doesn't look like I'm getting the correct results. I need to get both a count on distinct items regardless if they meet criteria or not, and then I need to get a distinct count on items that do meet criteria, and output both counts to the same row on the HTML table.

Any ideas?

Thanks!

December 20th, 2003, 04:28 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,743

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 18 m 16 sec
Reputation Power: 870

i used count(distinct Address) only because you did

perhaps you could try my query with count(*) as Totals instead

how many SignalQuality rows per addy?

in your last post you use the terminology "both a count on distinct items" and "a distinct count on items"

what in your mind is the difference?

and what is an item, an addy or a signalquality?

rudy

December 20th, 2003, 04:46 PM

roctech

Junior Member

Join Date: Oct 2003

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Perhaps if I explain what I am trying to do...

There are two tables...one contains street addresses...the other contains signal level information for cable modems...the different fields in the signal level table represent different RF values, like SNR and power levels. Each cable modem is polled multiple times per day, so when I want to know how many modems are below spec based on the criteria (SNR < 32, etc), I just want to see a count that shows each modem only once, perhaps the last entry it made in the table.

At the same time, I want to know how many distinct cable modems are actually in the table, regardless of levels. This would be filtered by joining the modems to their street address and looking at individual nodes, or areas.

All of these modems need to be matched up to their billing information, which is where the street address table comes in.

I hope this gives you a better idea of what I'm trying to do, and I really appreciate your help.

December 20th, 2003, 04:49 PM

roctech

Junior Member

Join Date: Oct 2003

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

BTW...The "Totals" part of the query works fine...the "Problems" part of the query is the issue...it is counting all entries when I need it to count just the distinct ones.

Here is the code with the proper tablenames. I changed the "Problems" part of the query back to filter in only those modems out-of-spec.

Code:

$query = "select addy.Node
     , addy.city
     , sum (
          case when CATVModemsignalquality.ModemTxPower not between 35 and 55
               or CATVModemsignalquality.ModemRxPower not between -10 and 11
               or CATVModemsignalquality.ModemRxSNR < 32
               or CATVModemsignalquality.CMTSRxPower not between -2 and 2
               or CATVModemsignalquality.CMTSRxSNR < 25
               and corp = '$Corp' and addy.mac = CATVModemSignalQuality.ModemMACAddress
             then 1
             else 0 end )      as Problems
     , count(distinct ModemMACAddress) as Total
  from addy
inner
  join CATVModemSignalQuality
    on addy.mac = CATVModemSignalQuality.ModemMACAddress
 where corp = '$Corp' and addy.mac = CATVModemSignalQuality.ModemMACAddress
group 
    by addy.Node
     , addy.city
order 
    by addy.city
     , Problems desc";

Last edited by roctech : December 20th, 2003 at 04:52 PM.

December 20th, 2003, 05:23 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,743

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 18 m 16 sec
Reputation Power: 870

you cannot put

and addy.mac = CATVModemSignalQuality.ModemMACAddress

into the SUM function

and you need not put it into the WHERE clause because it is a join condition already present in the ON clause

it seems you have two one-to-many relationships involved here -- multiple readins per modem, and possibly multiple modems per address

please confirm, because if this is so, you will need to nest a query

also, is ModemTxPower=40 bad or good? you've got me all mixed up with your NOTs and ORs

December 20th, 2003, 05:41 PM

roctech

Junior Member

Join Date: Oct 2003

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

A ModemTXPwr reading of 40 would be good, and should not be counted as a problem...however, the same modem might have a ModemRxSNR of 20, which is a problem, therefore the entire row for that modem needs to be counted and output as a problem. The only way for a modem not to be counted as a problem is if all five criteria are not met. If a modem meets four of the five criteria, then it has a problem.

Yes, one modem has many values associated with it, and it is very possibly that there will be more than one modem on a given account.

I removed the "addy.mac = CATVModemSignalQuality.ModemMACAddress" from those two places.

Thanks Rudy. I think this is close, only if it would count the distinct problems.

December 20th, 2003, 05:55 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,743

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 18 m 16 sec
Reputation Power: 870

Code:

select Node
     , city
     , count(*)                   as Modems
     , sum(
         case when Problems > 0
              then 1 else 0 end
          )                       as ProblemModems
  from (
       select addy.Node
            , addy.city
            , CATVModemSignalQuality.ModemMACAddress
            , sum (
                 case when CATVModemsignalquality.ModemTxPower not between 35 and 55
                      or CATVModemsignalquality.ModemRxPower not between -10 and 11
                      or CATVModemsignalquality.ModemRxSNR < 32
                      or CATVModemsignalquality.CMTSRxPower not between -2 and 2
                      or CATVModemsignalquality.CMTSRxSNR < 25
                      and corp = '$Corp' 
                    then 1
                    else 0 end )      as Problems
         from addy
       inner
         join CATVModemSignalQuality
           on addy.mac = CATVModemSignalQuality.ModemMACAddress
        where corp = '$Corp' 
       group 
           by addy.Node
            , addy.city
            , CATVModemSignalQuality.ModemMACAddress
       ) as ModemTable     
group 
    by Node
     , city
order 
    by Node
     , city

#10

December 20th, 2003, 06:07 PM

roctech

Junior Member

Join Date: Oct 2003

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks Rudy, that did the trick.

If you don't mind, can you break it down for me so I can understand the query and how it works?

I really appreciate your help

#11

December 20th, 2003, 06:48 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,743

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 18 m 16 sec
Reputation Power: 870

best way i can explain it is to run the inner query by itself, and print out the data

observe the number of problems per modem

then with a pencil, mark off the cities, and that way, get an idea of what the outer query's grouping and summing will do

rudy

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: