-3

I'm after some help, only just starting out in SQL.

I have 1 table which I am trying to run 2 selects from 2 columns, one is for a COUNT and the other is for a SUM, I can get them to run ok separately but when trying to get them as one query, this is when it fails.

the table holds lots of dates so my query only selects the last 3 days and counts up one column that is anything that is not blank, and the other select gets the last 3 dates and sums up anything over zero.

please see what I have so far,

For the COUNT.

SELECT   'u_telephone_data' As Type,
         u_call_date, 
         COUNT(u_dialled_digits),             
FROM     u_telephone_data  
WHERE    u_dialled_digits <>'' 
AND      u_call_date between dateadd(day, -3, curdate()) and curdate()  
GROUP BY u_call_date, u_dialled_digits

For the SUM

SELECT       'u_telephone_data' As Type,
             u_call_date, 
             SUM (u_calls_offered) 
FROM         u_telephone_data  
WHERE        u_calls_offered >= '0'
AND          u_call_date between dateadd(day, -3, curdate()) and curdate() 
GROUP BY     u_call_date, u_calls_offered

Sorry about the way its been displayed, hope it makes sense.

2

These two queries should be able to be merged without too much trouble.. Something like the below should do the trick:

SELECT   'u_telephone_data' As Type,
     u_call_date, 
     SUM(CASE when u_dialled_digits <> '' then 1 else 0 end) as dialled,
     SUM(CASE when u_calls_offered >= 0 then u_calls_offered else 0 end) as Offered
FROM     u_telephone_data  
WHERE    u_call_date between dateadd(day, -3, getdate()) and getdate()  
GROUP BY u_call_date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.