Solved

SQL query help with grouping and distinct

Posted on 2007-04-06
14
276 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Bradino
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
It works…. WahoOoo! Thank you very much for your expertise!
0
 

Author Comment

by:Bradino
Comment Utility
Truely a genius...
0
 
LVL 29

Expert Comment

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now