Possible to merge these two queries? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Possible to merge these two queries?

Author

Topic

dinosaurhead
Starting Member

10 Posts

Posted - 2011-04-02 : 07:27:40

Together these would achieve what I am aiming to query. I just dont seem to get them working together in the same query. Any ideas :)

select service.serviceid, service.servicename, customer.fname from service, customer, customer2service
WHERE (customer.customerid = customer2service.customerid AND service.serviceid = customer2service.serviceid)

select customer2service.branchid, SUM(service.cost) from service, customer, customer2service
WHERE (customer.customerid = customer2service.customerid AND service.serviceid = customer2service.serviceid) GROUP BY customer2service.branchid ORDER BY SUM(service.cost) DESC

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-02 : 07:53:51

try this:


select 
 	 s.serviceid
	,s.servicename
	,c.fname 
	,c2s.branchID
	,sum(s.cost) as service_costs
from 
service as s
join customer2service as c2s
on s.serviceID = c2s.serviceID
join customer as c
on c.customerID = c2s.customerID

group by
	 s.serviceid
	,s.servicename
	,c.fname 
	,c2s.branchID
order by service_costs desc

dinosaurhead
Starting Member

10 Posts

Posted - 2011-04-02 : 13:45:35

Hello,

Thanks for the recommendation.

select
service.serviceid
,service.servicename
,customer.fname
,customer2service.branchID
,sum(service.cost) as service_costs
from
service
join customer2service
on service.serviceID = customer2service.serviceID
join customer
on customer.customerID = customer2service.customerID

group by
service.serviceid
,service.servicename
,customer.fname
,customer2service.branchID
order by service_costs desc

Does work, but I need to group it all by branchids and then sort by the total sum of service costs per branch. Is this doable?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-03 : 16:29:31

>> I just don’t seem to get them working together in the same query. <<

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

Can I guess that what you call “service_id” is really a “service_code” since it is not a physical ENTITY? Do you actually have JUST one customer and one service, like your table names say? Is “Service_Orders” just a lazy name for service orders or tickets as well as a violation of a few other rules? And please don't tell me you think it is “link” or “junction” or some other non-RDBMS term.

SELECT S.service_code, S.service_name, C.first_name
FROM Services AS S,
Customers AS C,
Service_Orders AS SO
WHERE C.customer_id = SO.customer_id
AND S.service_code = SO.service_code;

Your second query puts Customers in the FROM clause and never uses them. Is this what you meant?

SELECT SO.branch_id, SUM(S.service_cost) AS service_cost_tot
FROM Services AS S,
Service_Orders AS SO
WHERE S.service_code = SO.service_code
GROUP BY SO.branch_id;

What do you want as a result? The vague phrase “work together” is not a specification. We can do a rollup for each customer showing all the branches at which got serviced. Is that what you meant?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

dinosaurhead
Starting Member

10 Posts

Posted - 2011-04-03 : 17:57:24

Thanks for your reply. I shall take onboard the criticism and when I have time I shall learn the proper formatting and naming from a book etc. etc.

Literally only been learning it for the past couple of weeks using college material.

What I wanted (excuse non-technical terminology) is to:

show the serviceid, servicename and customer details of all the services purchased from each branch. Group your output by branch and order output by sum of cost generated per branch – highest first!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources