Summarizing on different criteria within one table

  • I'm having some problems writing a SP to do some reporting on a table. I need to count the number of rows that meet one criteria and count the number of rows that meet another criteria (which is mutually exclusive from the other) and return each as a different column. I apologize for being so confusing. Here is an example of what I've done so far but isn't working.

    SELECT

    dbo.tblUser.iUserID,

    dbo.tblCampaign.iCampaignID,

    dbo.tblCall.sBTN,

    (SELECT count(dbo.tblCall.sBTN)

    FROM

    dbo.tblCall INNER JOIN dbo.tblCampaign

    ON dbo.tblCall.iCampaignID = dbo.tblCampaign.iCampaignID

    INNER JOIN dbo.tblProdToCamp

    ON dbo.tblCampaign.iCampaignID = dbo.tblProdToCamp.iCampaignID

    INNER JOIN dbo.tblProduct

    ON dbo.tblProdToCamp.iProductID = dbo.tblProduct.iProductID

    INNER JOIN dbo.tblUser

    ON dbo.tblCall.iUserID = dbo.tblUser.iUserID

    WHERE (dbo.tblProduct.szProdDesc LIKE '%Long Distance%')

    GROUP BY

    dbo.tblUser.iUserID,

    dbo.tblCall.sBTN,

    dbo.tblCampaign.iCampaignID),

    (SELECT count(dbo.tblCall.sBTN)

    FROM

    dbo.tblCall INNER JOIN dbo.tblCampaign

    ON dbo.tblCall.iCampaignID = dbo.tblCampaign.iCampaignID

    INNER JOIN dbo.tblProdToCamp

    ON dbo.tblCampaign.iCampaignID = dbo.tblProdToCamp.iCampaignID

    INNER JOIN dbo.tblProduct

    ON dbo.tblProdToCamp.iProductID = dbo.tblProduct.iProductID

    INNER JOIN dbo.tblUser

    ON dbo.tblCall.iUserID = dbo.tblUser.iUserID

    WHERE (dbo.tblProduct.szProdDesc LIKE '%Toll Free%')

    GROUP BY

    dbo.tblUser.iUserID,

    dbo.tblCall.sBTN,

    dbo.tblCampaign.iCampaignID)

    FROM

    dbo.tblCall INNER JOIN dbo.tblCampaign

    ON dbo.tblCall.iCampaignID = dbo.tblCampaign.iCampaignID

    INNER JOIN dbo.tblUser

    ON dbo.tblCall.iUserID = dbo.tblUser.iUserID

    GROUP BY

    dbo.tblCall.sBTN,

    dbo.tblCampaign.iCampaignID,

    dbo.tblUser.iUserID

    I'd appreciate any help.

    Thanks,

    Jason

  • Which part isnt working? The final group by looks weird but I have not tried it yet. Can you post ddl for the tables involved along with some insert statements to load some minimal test data?

    Andy

  • Sorry it took awhile to get back, I was having some problem getting on.

    Anyway, I kept working on trying to solve my problem and ended up using temp tables. I broke out my query into 3. I do the summarization on one criteria and use the INTO statement to create a temp table and repeat the same steps with the other criteria. Then I wrote one final SELECT to join the two temp tables to get one result set.

    I appreciate your time and help.

    Thanks,

    Jason

  • There's an easier way to do it. Use a statement like this:

    --

    SELECT dbo.tblUser.iUserID,

    dbo.tblCampaign.iCampaignID,

    dbo.tblCall.sBTN,

    COUNT(CASE WHEN dbo.tblProduct.szProdDesc LIKE '%Long Distance%' THEN 1 ELSE NULL END) AS LongDistance,

    COUNT(CASE WHEN dbo.tblProduct.szProdDesc LIKE '%Toll Free%' THEN 1 ELSE NULL END) AS TollFree

    FROM

    dbo.tblCall INNER JOIN dbo.tblCampaign

    ON dbo.tblCall.iCampaignID = dbo.tblCampaign.iCampaignID

    INNER JOIN dbo.tblProdToCamp

    ON dbo.tblCampaign.iCampaignID = dbo.tblProdToCamp.iCampaignID

    INNER JOIN dbo.tblProduct

    ON dbo.tblProdToCamp.iProductID = dbo.tblProduct.iProductID

    INNER JOIN dbo.tblUser

    ON dbo.tblCall.iUserID = dbo.tblUser.iUserID

    GROUP BY

    dbo.tblUser.iUserID,

    dbo.tblCall.sBTN,

    dbo.tblCampaign.iCampaignID

    --

    One statement, and it avoids temp tables and the like. You can add a WHERE clause, too, to narrow down your data if the table is large; I'd suggest something like:

    WHERE dbo.tblProduct.szProdDesc LIKE '%Toll Free%' OR dbo.tblProduct.szProdDesc LIKE '%Long Distance%'

    Give it a shot.

    Matthew Burr

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply