SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count help required
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MMC78
Starting Member

4 Posts

Posted - 10/04/2012 :  09:28:25  Show Profile  Reply with Quote
Hi All,

First post here so go easy on me. I'm also quite new to the world of SQL.

Basically, I have a query that returns a result set from various tables via JOINS.

Once I run this query I get circa 15,000 records.

Here is a copy of the query..

SELECT t.task_id "Task ID",
t.pro_id "Process ID",
p.pro_description "Process Description",
p.prog_grp_id "Scan Batch ID",
pg.grp_description "Scan Batch Desc",
p.pro_prc_id "Process Category ID",
prc_description "Process Category Desc",
ch.chk_id "Checklist ID",
ch.chk_name "Checklist Description",
p.pro_default_que_id "Default Queue",
t.que_id "Queue ID",
q.que_description "Queue Description",
t.product_group_id "Product ID",
pg.product_group_desc "Product Description",
t.system_id "System ID",
s.system_desc "System Description",
t.priority_curve_id "Priortity Curve ID",
pc.priority_curve_desc "Priority Curve Desc",
tv.unit_time "Unit Time",
sa.service_agreement_id "SLA ID",
sa.service_agreement_desc "SLA Desc",
tv.ote_id "Team ID",
team.ote_team_name "Team Name",
department.ogr_group_name "Dept Name"
FROM tasks t
JOIN wfl_processes p ON t.pro_id = p.pro_id
JOIN wfl_queues q ON t.que_id = q.que_id
JOIN wfl_process_groups pg ON p.prog_grp_id = pg.grp_id
JOIN wfl_proc_cat pc ON p.pro_prc_id = pc.prc_id
JOIN wfl_checklists ch ON p.pro_chk_id = ch.chk_id
JOIN task_versions tv ON t.current_task_version_id = tv.task_version_id
JOIN wfl_org_teams team ON tv.ote_id = team.ote_id
JOIN wfl_org_groups department ON team.ote_ogr_id = department.ogr_id
JOIN systems s ON s.system_id = t.system_id
JOIN product_groups pg ON pg.product_group_id = t.product_group_id
JOIN priority_curves pc ON pc.priority_curve_id = t.priority_curve_id
JOIN service_agreements sa ON sa.service_agreement_id = tv.company_sla_version_id
JOIN company_sla_versions csv ON csv.company_sla_version_id = tv.company_sla_version_id
WHERE sa.service_agreement_id = 151;

I want to group the result set via a count on the sa.service_agreement_id field.

I've messed about with the count (*) and group by functions but can't seem to get it to work.

Any feedback would be appreciated!

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 10/04/2012 :  09:41:23  Show Profile  Visit webfred's Homepage  Reply with Quote
I want to group the result set via a count on the sa.service_agreement_id field.
That isn't clear.
Can you please provide some sample rows and then the wanted result to make it more clear?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 10/04/2012 :  09:41:49  Show Profile  Reply with Quote
Try this.........


SELECT t.task_id "Task ID",
t.pro_id "Process ID",
p.pro_description "Process Description",
p.prog_grp_id "Scan Batch ID",
pg.grp_description "Scan Batch Desc",
p.pro_prc_id "Process Category ID",
prc_description "Process Category Desc",
ch.chk_id "Checklist ID",
ch.chk_name "Checklist Description",
p.pro_default_que_id "Default Queue",
t.que_id "Queue ID",
q.que_description "Queue Description",
t.product_group_id "Product ID",
pg.product_group_desc "Product Description",
t.system_id "System ID",
s.system_desc "System Description",
t.priority_curve_id "Priortity Curve ID",
pc.priority_curve_desc "Priority Curve Desc",
tv.unit_time "Unit Time",
sa.service_agreement_id "SLA ID",
COUNT( sa.service_agreement_id ) over() as COUNTS,
sa.service_agreement_desc "SLA Desc",
tv.ote_id "Team ID",
team.ote_team_name "Team Name",
department.ogr_group_name "Dept Name"
FROM tasks t
JOIN wfl_processes p ON t.pro_id = p.pro_id
JOIN wfl_queues q ON t.que_id = q.que_id
JOIN wfl_process_groups pg ON p.prog_grp_id = pg.grp_id
JOIN wfl_proc_cat pc ON p.pro_prc_id = pc.prc_id
JOIN wfl_checklists ch ON p.pro_chk_id = ch.chk_id
JOIN task_versions tv ON t.current_task_version_id = tv.task_version_id
JOIN wfl_org_teams team ON tv.ote_id = team.ote_id
JOIN wfl_org_groups department ON team.ote_ogr_id = department.ogr_id
JOIN systems s ON s.system_id = t.system_id
JOIN product_groups pg ON pg.product_group_id = t.product_group_id
JOIN priority_curves pc ON pc.priority_curve_id = t.priority_curve_id
JOIN service_agreements sa ON sa.service_agreement_id = tv.company_sla_version_id
JOIN company_sla_versions csv ON csv.company_sla_version_id = tv.company_sla_version_id
WHERE sa.service_agreement_id = 151;


--
Chandu
Go to Top of Page

MMC78
Starting Member

4 Posts

Posted - 10/04/2012 :  09:51:35  Show Profile  Reply with Quote
quote:
Originally posted by webfred

I want to group the result set via a count on the sa.service_agreement_id field.
That isn't clear.
Can you please provide some sample rows and then the wanted result to make it more clear?


Too old to Rock'n'Roll too young to die.



Thanks for your reply. Apologies for the lack of clarity.

Ok, so if I run my query above I will get 15,000 results.

In the column sa.service_agreement_id there are four different values that are used that make up the 15,000. What I want to do is get a count of how many of each value is in the result set. For example.

sa.service_agreement_id VALUE 1 = 1000
sa.service_agreement_id VALUE 2 = 10000
sa.service_agreement_id VALUE 3 = 2000
sa.service_agreement_id VALUE 4 = 2000
TOTAL = 15000
Go to Top of Page

MMC78
Starting Member

4 Posts

Posted - 10/04/2012 :  09:55:22  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Try this.........


SELECT t.task_id "Task ID",
t.pro_id "Process ID",
p.pro_description "Process Description",
p.prog_grp_id "Scan Batch ID",
pg.grp_description "Scan Batch Desc",
p.pro_prc_id "Process Category ID",
prc_description "Process Category Desc",
ch.chk_id "Checklist ID",
ch.chk_name "Checklist Description",
p.pro_default_que_id "Default Queue",
t.que_id "Queue ID",
q.que_description "Queue Description",
t.product_group_id "Product ID",
pg.product_group_desc "Product Description",
t.system_id "System ID",
s.system_desc "System Description",
t.priority_curve_id "Priortity Curve ID",
pc.priority_curve_desc "Priority Curve Desc",
tv.unit_time "Unit Time",
sa.service_agreement_id "SLA ID",
COUNT( sa.service_agreement_id ) over() as COUNTS,
sa.service_agreement_desc "SLA Desc",
tv.ote_id "Team ID",
team.ote_team_name "Team Name",
department.ogr_group_name "Dept Name"
FROM tasks t
JOIN wfl_processes p ON t.pro_id = p.pro_id
JOIN wfl_queues q ON t.que_id = q.que_id
JOIN wfl_process_groups pg ON p.prog_grp_id = pg.grp_id
JOIN wfl_proc_cat pc ON p.pro_prc_id = pc.prc_id
JOIN wfl_checklists ch ON p.pro_chk_id = ch.chk_id
JOIN task_versions tv ON t.current_task_version_id = tv.task_version_id
JOIN wfl_org_teams team ON tv.ote_id = team.ote_id
JOIN wfl_org_groups department ON team.ote_ogr_id = department.ogr_id
JOIN systems s ON s.system_id = t.system_id
JOIN product_groups pg ON pg.product_group_id = t.product_group_id
JOIN priority_curves pc ON pc.priority_curve_id = t.priority_curve_id
JOIN service_agreements sa ON sa.service_agreement_id = tv.company_sla_version_id
JOIN company_sla_versions csv ON csv.company_sla_version_id = tv.company_sla_version_id
WHERE sa.service_agreement_id = 151;


--
Chandu



Thanks for your prompt reply.

This doesn't seem to have worked. All that has happened here is that a new column called COUNTS has been added to the result. There are still 15000 rows returned and the data for each row in your new column is the same for every row (it's quoting 15000 on every row - so basically just a count of the total).
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 10/04/2012 :  09:58:51  Show Profile  Reply with Quote
But your WHERE condition gives only one service_agreement_id. So you can get only one count.
'On which criteria you need to get count?'

--
Chandu
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 10/04/2012 :  10:00:06  Show Profile  Visit webfred's Homepage  Reply with Quote
change

over()

to

over(partition by sa.service_agreement_id)


Too old to Rock'n'Roll too young to die.
Go to Top of Page

MMC78
Starting Member

4 Posts

Posted - 10/04/2012 :  10:21:24  Show Profile  Reply with Quote
Sorry both! I've quoted something incorrect!

I want a count on the pc.priority_curve_desc not the sa.service_agreement_id!

My where clause correctly only quotes one sa.service_agreement_id.

From the circa 15,000 results there are four b]pc.priority_curve_desc[/b] values that I want to count and group by.

I've just done this in a pivot table in excel and the results look like this....

Count of SLA Desc
Priority Curve Desc Total

Glasgow 5 Day 1
Glasgow Standard Curve 15187
Max Priority 595
Standard (6 Day) 48
Grand Total 15831

how would I do this in SQL to avoid having to export the data?

Once again, apologies for the incorrect info earlier!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/04/2012 :  10:37:53  Show Profile  Reply with Quote
Apply GROUP BY over relevant fields and apply COUNT() over pc.priority_curve_desc

(I dont know the exact field names so cant suggest anything more with limited info provided)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 10/05/2012 :  01:51:46  Show Profile  Reply with Quote

SELECT t.task_id "Task ID",
t.pro_id "Process ID",
p.pro_description "Process Description",
p.prog_grp_id "Scan Batch ID",
pg.grp_description "Scan Batch Desc",
p.pro_prc_id "Process Category ID",
prc_description "Process Category Desc",
ch.chk_id "Checklist ID",
ch.chk_name "Checklist Description",
p.pro_default_que_id "Default Queue",
t.que_id "Queue ID",
q.que_description "Queue Description",
t.product_group_id "Product ID",
pg.product_group_desc "Product Description",
t.system_id "System ID",
s.system_desc "System Description",
t.priority_curve_id "Priortity Curve ID",
pc.priority_curve_desc "Priority Curve Desc",
COUNT( pc.priority_curve_desc ) OVER (PARTITION BY pc.priority_curve_desc) AS Counts,
tv.unit_time "Unit Time",
sa.service_agreement_id "SLA ID",
sa.service_agreement_desc "SLA Desc",
tv.ote_id "Team ID",
team.ote_team_name "Team Name",
department.ogr_group_name "Dept Name"
FROM tasks t
JOIN wfl_processes p ON t.pro_id = p.pro_id
JOIN wfl_queues q ON t.que_id = q.que_id
JOIN wfl_process_groups pg ON p.prog_grp_id = pg.grp_id
JOIN wfl_proc_cat pc ON p.pro_prc_id = pc.prc_id
JOIN wfl_checklists ch ON p.pro_chk_id = ch.chk_id
JOIN task_versions tv ON t.current_task_version_id = tv.task_version_id
JOIN wfl_org_teams team ON tv.ote_id = team.ote_id
JOIN wfl_org_groups department ON team.ote_ogr_id = department.ogr_id
JOIN systems s ON s.system_id = t.system_id
JOIN product_groups pg ON pg.product_group_id = t.product_group_id
JOIN priority_curves pc ON pc.priority_curve_id = t.priority_curve_id
JOIN service_agreements sa ON sa.service_agreement_id = tv.company_sla_version_id
JOIN company_sla_versions csv ON csv.company_sla_version_id = tv.company_sla_version_id
WHERE sa.service_agreement_id = 151;



--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000