• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

MS access, SQL question

I have this SQL:

SELECT *, sum(work_order_item.quantity)    from work_order
left join work_order_item ON work_order_item.local_work_order_id = work_order.local_id
where work_order.local_id = 36

It will only ever return one record.  How can I write this query without having to list all the fields in the work_order table?
0
HLRosenberger
Asked:
HLRosenberger
  • 4
  • 2
1 Solution
 
Ryan ChongCommented:
How can I write this query without having to list all the fields in the work_order table?

remove the Select *

SELECT sum(work_order_item.quantity)    from work_order
left join work_order_item ON work_order_item.local_work_order_id = work_order.local_id
where work_order.local_id = 36

Open in new window

?
or select yourfield1, youfield2, sum(work_order_item.quantity)    from work_order
...
with a group by of your fields
SELECT yourfield1, youfield2, sum(work_order_item.quantity)    from work_order
left join work_order_item ON work_order_item.local_work_order_id = work_order.local_id
where work_order.local_id = 36
group by yourfield1, youfield2

Open in new window

0
 
PatHartmanCommented:
When you aggregate data, you cannot select columns that "unaggregate".  For example, if you want to count customers in a state, the query would be:
Select State, Count(*) AS CustCount
Group By State;

If you included CustName
Select CustName,, State, Count(*) AS CustCount
Group By CustName, State;

Your CustCount would always be 1 because no aggregation would take place.  That is what your Select * is doing.
0
 
HLRosenbergerAuthor Commented:
So, if I have say 20 columns to pull back from the main table, and then 2 column from a JOINed table that I want to SUM, I have to list all the 20 columns and GROUP BY all of them?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HLRosenbergerAuthor Commented:
Also what I really want to do is have a grand total cost.   I believe I know how to do this using SQL server, but not Access SQL.    I have a main orders table and a related orders item, which has  a quantity and cost.  I want a grand total for the order.  Trying to do it all in in SELECT statement, where I pull into from the  main orders table, and then calculate the total cost.  In SQL server, I think a Sub-query select would work.
0
 
HLRosenbergerAuthor Commented:
ah, this works.  I just need to list all columns in the orders table.   There is around 30 columns. No way around this, correct?  

SELECT  work_order.id,  work_order.service_order_id,  work_order.equipment_id,  work_order.description, sum(work_order_item.quantity * work_order_item.unit)

FROM  work_order

inner join work_order_item ON work_order_item.local_work_order_id = work_order.local_id

where work_order.id = 75567

group by work_order.id,  work_order.service_order_id,  work_order.equipment_id,  work_order.description
0
 
PatHartmanCommented:
It is always better to select specific columns than to use Select * anyway but when you are aggregating, EVERY column in the Select clause must be accounted for.  If you are not summing, counting, etc, then you must specifically group by.
0
 
HLRosenbergerAuthor Commented:
Thanks!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now