Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count help required

Author  Topic 

MMC78
Starting Member

4 Posts
Posted - 2012-10-04 : 09:28:25
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
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2012-10-04 : 09:41:23
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
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2012-10-04 : 09:41:49
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 - 2012-10-04 : 09:51:35
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 - 2012-10-04 : 09:55:22
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
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2012-10-04 : 09:58:51
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
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2012-10-04 : 10:00:06
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 - 2012-10-04 : 10:21:24
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

52326 Posts
Posted - 2012-10-04 : 10:37:53
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
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2012-10-05 : 01:51:46

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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site