Solved

SQL query help with grouping and distinct

Posted on 2007-04-06
14
283 Views
Last Modified: 2013-12-24
Experts,

I’m creating a report with Coldfusion / MS SQL and am having trouble getting the data that I want.

Here is the table:
Result2 (table name)
Id (int - PK)
Rpt_dt (smalldatetime)
Tot_col (int)
LT_24_hr (int)
Lt_24_avg (decimal 18,2)
Cum_wk (decimal 18,2)
Weekly_cum (varchar)

Sample Data:
Id     Rpt_dt          Tot_cld     LT_24_hr     Lt_24_avg     Cum_wk     Weekly_cum
347  3/23/2007     2511      1652              65.79            62.29       03/23/07 - 03/29/07
348  3/25/2007     670      537      80.15              68.81            68.81          03/23/07 - 03/29/07
349  3/24/2007     1450      927              63.93            67.29       03/23/07 - 03/29/07
350  3/26/2007     2374      1314              55.35            63.24       03/23/07 - 03/29/07
351  3/27/2007     2190      1291              58.95            62.22       03/23/07 - 03/29/07
352  3/28/2007     2739      1785              65.17            62.9       03/23/07 - 03/29/07
353  3/29/2007     2113          1360               64.36             63.12        03/23/07 - 03/29/07
354  3/30/2007     2263          1438               63.54             63.12        03/23/07 - 03/29/07
355  3/31/2007     1586      1031               65.01             64.15        03/30/07 - 04/05/07
356  4/1/2007           544                  486               89.34             67.27        03/30/07 - 04/05/07
357  4/2/2007           2372      1310               55.23             63.05        03/30/07 - 04/05/07
358  4/3/2007           2436      1423               58.42             61.82        03/30/07 - 04/05/07
359  4/4/2007           2355      1491               63.31             62.12        03/30/07 - 04/05/07
360  4/5/2007           2831      1977               69.83             63.64        03/30/07 - 04/05/07

Query Statement:
SELECT     sum(*)
FROM         results2
WHERE     (weekly_cum =
                          (SELECT DISTINCT (weekly_cum)
                            FROM   results2))


Sorry for the lengthy sample data, but I wanted to provide as such info as possible. I will try to explain this best that I can. What I want to do is to get the sum of (tot_cld, lt_24_hr, lt_24_avg) where weekly_cum are the same. Not sure if I’m suppose to group it, please advise.

The out put should show only two weekly_cum date
•      03/23/07 - 03/29/07 with the sum of (tot_cld, lt_24_hr, lt_24_avg)
•      03/30/07 - 04/05/07 with the sum of (tot_cld, lt_24_hr, lt_24_avg)

Thank you in advance for you help!

Regards,
Bradino
0
Comment
Question by:Bradino
  • 8
  • 6
14 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18865194
Try this:

SELECT
  weekly_cum,
  ISNULL(sum(tot_cld),0)+ISNULL(sum(lt_24_hr),0)+ISNULL(sum(lt_24_avg),0) as Totals
FROM
  results2
GROUP BY
  weekly_cum
0
 

Author Comment

by:Bradino
ID: 18865460
Thanks you! But not quit there, I have made minor changes to the query but it not come out correctly. Could you please take a look at my query and give me your expert advise. I wanted to dive the two sum() tot_cld and lt_24_hr to get the tot_24_avg. Not sure if it's even possible to do what i want it do to.

SELECT     weekly_cum, ISNULL(SUM(tot_cld), 0) AS tot_cld, ISNULL(SUM(lt_24_hr), 0) AS lt_24_hr, ISNULL(SUM(lt_24_hr), 0) / ISNULL(SUM(tot_cld), 0)
                      AS tot_24_avg
FROM         results2
GROUP BY weekly_cum

Thank you so much for you assistance!
0
 

Author Comment

by:Bradino
ID: 18865468
I can get the desired result with coldfusion code but was just checking to see if I can get SQL server to do it for me.

Regards,
0
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
LVL 29

Expert Comment

by:Nightman
ID: 18866314
Sorry, I was offline for a while. SQL can return the one divided by the other, but I'm a little unclear as to what you are trying to achieve.
0
 

Author Comment

by:Bradino
ID: 18867167
Sorry, for the confusing. I will try to be more clear.

I want to get the average percentage of ticket closed in with 24 hours with in a week span (weekly_cum). I do this by diving total ticket closed under 24 hours (lt_24_hr) by total closed (tot_cld).

Example:
LT_24_hr: 1977
Tot_cld: 2831

1977/2831 = 69.83%


Thanks!!
0
 

Author Comment

by:Bradino
ID: 18867178
Again i'm able to do this in coldfusion but wondering if I can get SQl server to go it instead.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18867201
It should just be

(ISNULL(SUM(lt_24_hr), 0) / ISNULL(SUM(tot_cld), 0)) * 100.00

e.g.

SELECT
  weekly_cum,
  ISNULL(SUM(tot_cld), 0) AS tot_cld,
  ISNULL(SUM(lt_24_hr), 0) AS lt_24_hr,
  (ISNULL(SUM(lt_24_hr), 0) / ISNULL(SUM(tot_cld), 0)) * 100 as tot_24_avg
FROM
  results2
GROUP BY
  weekly_cum
0
 

Author Comment

by:Bradino
ID: 18867255
I've try that and it always come out to "0" zero. here is the output

03/11/07 - 03/17/07: 5020 | 3317 | 0%
03/16/07 - 03/22/07: 5091 | 3171 | 0%
03/23/07 - 03/29/07: 4376 | 2798 | 0%
03/30/07 - 04/05/07: 12305 | 8074 | 0%

regards,
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18867271
That's because of what's known as integer division. Try it like this:

SELECT
  weekly_cum,
  ISNULL(SUM(tot_cld), 0) AS tot_cld,
  ISNULL(SUM(lt_24_hr), 0) AS lt_24_hr,
  (ISNULL(SUM(lt_24_hr), 0.00) / CAST(ISNULL(SUM(tot_cld), 0.00)) as decimal(18,2)) * 100.00 as tot_24_avg
FROM
  results2
GROUP BY
  weekly_cum
0
 

Author Comment

by:Bradino
ID: 18867309
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'CAST', expected 'AS'.  

Here is my complete qyery string:
SELECT     weekly_cum,
               ISNULL(SUM(tot_cld), 0) AS tot_cld,
               ISNULL(SUM(lt_24_hr), 0) AS lt_24_hr,
               (ISNULL(SUM(lt_24_hr), 0.00)/CAST(ISNULL(SUM(tot_cld), 0.00)) as decimal(18,2))* 100.00 as lt_24_avg
FROM         east_results2
GROUP BY weekly_cum
0
 
LVL 29

Accepted Solution

by:
Nightman earned 250 total points
ID: 18867329
Sorry, too many parenthises, tired eyes :)

SELECT
  weekly_cum,
  ISNULL(SUM(tot_cld), 0) AS tot_cld,
  ISNULL(SUM(lt_24_hr), 0) AS lt_24_hr,
  ISNULL(SUM(lt_24_hr), 0.00)/CAST(ISNULL(SUM(tot_cld), 0.00) as decimal(18,2))* 100.00 as lt_24_avg
FROM
  east_results2
GROUP BY
  weekly_cum
0
 

Author Comment

by:Bradino
ID: 18867371
It works…. WahoOoo! Thank you very much for your expertise!
0
 

Author Comment

by:Bradino
ID: 18867374
Truely a genius...
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18867392
It's a pleasure - good luck with your project :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question