[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL query help with grouping and distinct

Posted on 2007-04-06
14
Medium Priority
?
290 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.
Suggested Courses

649 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