Select statement with multiple count calculations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement with multiple count calculations

Author

Topic

SQLN00b11
Starting Member

6 Posts

Posted - 2011-03-07 : 17:39:58

Hi Everyone,

I'm new to T-SQL and have only a basic grasp of it so far. I've been scratching my head trying to work out the following and would really appreciate if someone could tell me the most straight forward way to output my result.

I am trying to summarise data of one table where there are multiple phone call stats for a report. Each number that is called can appear more than once and can return one of 3 signals. For each number I want to shows stats showing total calls, count for each type of signal and the failure as percentage. So for each number there should be just one row showing the complete call stats. I am a querying the table for call stats after a particular date.

Below is part of my Calltable that i am getting the results from.

Number SignalCode
64566 U
64566 U
62358 C
95431 C
45686 D
45686 C

I want the results to show the following for each number:

Number | Total No Calls | U SignalCode | C SignalCode| D SignalCode

I have started off a query which returns the total calls for each number but don't know how to include the other stats and calculations in the same row. I will need to include a failure percentage rate for each number too which is calculated by (U signalcode + C signalcode/Total Calls).

I have started off the query to workout total no calls for each number but stuck on the nex steps:

--TOTAL NO CALLS
From CallTable
where starttime > '2011-03-04 00:00:00' and endtime < '2011-03-05 00:00:00'
group by Number,SignalCode
order by Number,SignalCode

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-07 : 17:51:30

We do not put “table” in a table name; it is silly and violates ISO-11179 rules about meta data. We have a DATE data type you can use. Please post DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

SELECT phone_nbr, COUNT(*) AS call_tot,
SUM(CASE WHEN signal_code = 'U' THEN 1 ELSE 0 END) AS u_tot,
SUM(CASE WHEN signal_code = 'C' THEN 1 ELSE 0 END) AS c_tot,
SUM(CASE WHEN signal_code = 'D' THEN 1 ELSE 0 END) AS d_tot
FROM CallLog
WHERE start_time >= '2011-03-04'
AND end_time =< '2011-03-05'
GROUP BY phone_nbr;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-03-07 : 21:45:57

No sample data was provided for the dates and so I omitted the WHERE clause conditions testing those values.


-- The primary key was omitted for brevity.
CREATE TABLE CallTable
(Number         INTEGER
,SignalCode     NVARCHAR(1)
)

INSERT INTO CallTable
SELECT 64566, 'U' UNION ALL
SELECT 64566, 'U' UNION ALL
SELECT 62358, 'C' UNION ALL
SELECT 95431, 'C' UNION ALL
SELECT 45686, 'D' UNION ALL
SELECT 45686, 'C'

  SELECT C1.Number
        ,COUNT(C1.Number)
        ,SUM(CASE WHEN C1.SignalCode = 'U' THEN 1 ELSE 0 END) AS [U SignalCode]
        ,SUM(CASE WHEN C1.SignalCode = 'C' THEN 1 ELSE 0 END) AS [C SignalCode]
        ,SUM(CASE WHEN C1.SignalCode = 'D' THEN 1 ELSE 0 END) AS [D SignalCode]
        ,CAST((SUM(CASE WHEN C1.SignalCode = 'U' THEN 1.0 ELSE 0.0 END) +
               SUM(CASE WHEN C1.SignalCode = 'D' THEN 1.0 ELSE 0.0 END)) /
              CAST(COUNT(C1.Number) AS DECIMAL(18,2)) * 100 AS DECIMAL(18,2)) AS [Failure Percentage]
    FROM CallTable AS C1
GROUP BY C1.Number

DROP TABLE CallTable

/*
Number      Total No Calls U SignalCode C SignalCode D SignalCode Failure Percentage
----------- -------------- ------------ ------------ ------------ -------------------
45686       2              0            1            1            50.00
62358       1              0            1            0            0.00
64566       2              2            0            0            100.00
95431       1              0            1            0            0.00
*/

SQLN00b11
Starting Member

6 Posts

Posted - 2011-03-08 : 07:37:30

Thanks guys, I really appreciate that you both took the time to provide a solution. Chris I am not permitted to amend the databases to pull up the stats for the report I need so did not use the Create Table.

I see that both your's and Jcelko's select statement are very similar. I based my select statement on Jcelko but tried to combine Chris's percentage calculation into the mix but was getting errors.

Could someone show how the percentage stats can be produced using jcelko's code?

Many thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-08 : 07:49:01

SELECT Number, COUNT(*) AS call_tot,
SUM(CASE WHEN signalcode = 'U' THEN 1 ELSE 0 END)*1.0E/count(*) AS u_tot,
SUM(CASE WHEN signalcode = 'C' THEN 1 ELSE 0 END)*1.0E/count(*) AS c_tot,
SUM(CASE WHEN signalcode = 'D' THEN 1 ELSE 0 END)*1.0E/count(*) AS d_tot
FROM CallTable
GROUP BY Number ;

I am multiplying by 1.0E since an integer divided by an integer is an integer, which would make all your answers 0.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources