Solved

SQL query help with grouping and distinct

Posted on 2007-04-06
14
279 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

16 Experts available now in Live!

Get 1:1 Help Now