#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    13
    Rep 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!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    13
    Rep 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!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    13
    Rep 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.
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    13
    Rep 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.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    13
    Rep 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.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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
  18. #10
  19. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    13
    Rep 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
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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

IMN logo majestic logo threadwatch logo seochat tools logo