Post reply

Query to show the names of the ServiceTypes that have more customers via Web form than Telephone

  • January 12, 2015 at 1:34 pm

    #300189

    Hello, I can write basic queries but am struggling with this. In a table of:

    Table: Production

    ServiceType, CommunicationType, CustomerQty

    ------------------------------------------------------------

    ICT, Telephone, 500

    Cleaning, Web form, 600

    Support, On site, 35

    ICT, Web form, 300

    Cleaning, On site, 40

    Support, Telephone, 400

    ICT, On site, 100

    Cleaning, Telephone, 200

    Support, Web form, 900

    I need to show the names of the ServiceTypes that have more customers via Web form than Telephone. Any pointers would be greatly appreciated.

  • January 12, 2015 at 1:46 pm

    #1769636

    /*--Results

    ServiceTypeWebQtyTelQty

    Cleaning600200

    Support900400

    */

    ;WITH MyCTE([ServiceType],[CommunicationType],[CustomerQty])

    AS

    (

    SELECT 'ICT','Telephone',500 UNION ALL

    SELECT 'Cleaning','Web form',600 UNION ALL

    SELECT 'Support','On site',35 UNION ALL

    SELECT 'ICT','Web form',300 UNION ALL

    SELECT 'Cleaning','On site',40 UNION ALL

    SELECT 'Support','Telephone',400 UNION ALL

    SELECT 'ICT','On site','100' UNION ALL

    SELECT 'Cleaning','Telephone',200 UNION ALL

    SELECT 'Support','Web form',900

    )

    SELECT T1.[ServiceType],SUM(T1.[CustomerQty]) As WebQty,

    SUM(T2.[CustomerQty]) AS TelQty

    FROM MyCTE T1

    INNER JOIN MyCTE T2 ON T1.[ServiceType] = T2.ServiceType

    where T1.[CommunicationType] = 'Web form'

    AND T2.[CommunicationType] = 'Telephone'

    GROUP BY T1.[ServiceType]

    HAVING SUM(T1.[CustomerQty]) > SUM(T2.[CustomerQty])

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 12, 2015 at 1:54 pm

    #1769637

    Many thanks for your speedy reply!

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

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