?
Solved

SQL query help with grouping and distinct

Posted on 2007-04-06
14
Medium Priority
?
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

 
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 1000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

What You Need to Know when Searching for a Webhost Provider
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

770 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