sql sum prob

[mthompo]

hi have the following sql which works ok
i need to sum stdtime to give a total for each group
that is a sum for
mot.bayid = 1 AND mot.baytypeid =1
and a sum for
mot.bayid = 2 AND mot.baytypeid =1
etc...

i also need a total sum for the above sums?

motsql1 = "SELECT mot.mott, car.carid, car.reg, book.bookid, book.bookdate, book.waittick, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, sum(stdwork.stdtime) AS stdtime,  "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = "&[datesql] _
& " LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "LEFT JOIN stdworklink ON stdworklink.bookid = book.bookid "_
& "LEFT JOIN stdwork ON stdwork.stdworkid = stdworklink.stdworkid "_
& "WHERE mot.dayid ="&[motdaysql] _
& " AND ((mot.bayid = 1 AND mot.baytypeid =1) or "_
& "(mot.bayid = 2 AND mot.baytypeid =1) or "_
& "(mot.bayid = 1 AND mot.baytypeid =2) or "_
& "(mot.bayid = 2 AND mot.baytypeid =2) or "_
& "(mot.bayid = 3 AND mot.baytypeid =3)) "_
& "GROUP BY mot.motid, mot.mott, mot.bayid, mot.baytypeid "_
& "ORDER BY mot.motid"

 

[rac2]

Use a CASE expression or an IF() function to filter rows for the different sums. See

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

SELECT SUM( 
     IF( mot.bayid = 1 AND mot.baytypeid = 1 ,    
     stdwork.stdtime, 0 )  )
...

or

SELECT SUM( 
     CASE WHEN mot.bayid = 1 AND mot.baytypeid = 1 
            THEN stdwork.stdtime
          ELSE 0
     END )
...

 

[mthompo]

hi - thanks for your reply

can you tell me how to sum stdwork.stdtime for All records that have mot.bayid = 1 AND mot.baytypeid = 1
at the moment this sums for each individual record

 

[rac2]

The breakdown or subtotals are controlled with the GROUP BY clause. Each column mentioned in the GROUP BY list becomes a category for the SUMs. The GROUP BY produces a sum for every combination of values from the listed columns. If one of the columns is the record id, the sums will be calculated for each record.

If you wish to obtain a grand total over all rows, then do not GROUP BY.

I reread your original post and I am thinking maybe you just need to group by bayid and baytypeid instead of using the CASE expressions. Try this

motsql1 = "SELECT mot.mott, car.carid, car.reg, book.bookid, book.bookdate, book.waittick, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, sum(stdwork.stdtime) AS stdtime,  "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = "&[datesql] _
& " LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "LEFT JOIN stdworklink ON stdworklink.bookid = book.bookid "_
& "LEFT JOIN stdwork ON stdwork.stdworkid = stdworklink.stdworkid "_
& "WHERE mot.dayid ="&[motdaysql] _
& " AND ((mot.bayid = 1 AND mot.baytypeid =1) or "_
& "(mot.bayid = 2 AND mot.baytypeid =1) or "_
& "(mot.bayid = 1 AND mot.baytypeid =2) or "_
& "(mot.bayid = 2 AND mot.baytypeid =2) or "_
& "(mot.bayid = 3 AND mot.baytypeid =3)) "_
& "GROUP BY mot.bayid, mot.baytypeid "_
& "ORDER BY mot.bayid, mot.baytypeid"

Can a given bayid have many baytypeid's?
The reason I ask is that typically a column named something_id identifies a particular Something, while a column named somethingtypeid classifies that thing. Used in the table where Somethings are stored and described, things fall into a single category. In other words I would expect that a Bay would be some one type of Bay.

The implication for GROUP BY, and ORDER BY is that the order of the results will be determined only by bayid; baytypeid will not affect the order because given the bayid, there will be a single baytypeid. So you may wish to

...
& "GROUP BY mot.baytypeid, mot.bayid "_
& "ORDER BY mot.baytypeid, mot.bayid"

This will show all of the Bays of Type 1 together, followed by those of Type 2, etc.

 

[guelphdad]

The group by clause is incorrect and will produce unpredictable results. Any non-aggregate column in the SELECT needs to appear in the GROUP BY clause.

 

[rac2]

Hello quelphdad,

Right you are. Except MySQL allows that, possibly as a convenience. The entry in the reference manual is intriquing.

MySQL extends the use of GROUP BY to allow selecting fields that are not mentioned in the GROUP BY clause. If you are not getting the results that you expect from your query, please read the description of GROUP BY found in Section 12.10, “Functions and Modifiers for Use with GROUP BY Clauses”.

See

http://dev.mysql.com/doc/refman/5.0/en/select.html

I like the part about "not getting the results that you expect". I didnt find any further discussion of what kind of unexpected results we might get.

In any case, I agree with your advice. But looking at mthumpos query, there is a lot of work to get that all sorted out into proper SQL.

BTW, I lived in Guelph for two years. I loved everything about Canada except the long gray winters which last about 10 months. Regards.

 

[r937]

4 months of winter(*) not 10, months with mainly clear blue skies with a blazing sun (but don't tell anybody, or else we'll have even more people try to move up here)

would you like to see an article giving examples of the unexpected results? to nail home the principle of how GROUP BY actually works?


(*) summer: 5 months
winter: 4 months
fall: 3 months
spring: 1 week



r937.com | rudy.ca

 

[mthompo]

hi - thanks for your replies
rac2 - "If you wish to obtain a grand total over all rows, then do not GROUP BY."
does this mean i will need a new RS for a grand total?
- bayid is misleading it should be baynumber
- the case statements dont seem to do anything so have taken them out and im stuck back where i started not knowing how to sum All stdwork.stdtime where bayid=1/baytypeid=1?
-if i change the group by at all the column bayid=3/baytype=3 mucks up and i only get the first record in the RS.

guelphdad/r937 - glad weather is good in canada, in the uk its 11months winter/1month summer!

sql now looks like

motsql1 = "SELECT mot.mott, car.carid, car.reg, book.bookid, book.bookdate, book.waittick, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, SUM(stdwork.stdtime) AS stdtime, SUM(CASE WHEN mot.bayid = 1 AND mot.baytypeid = 1 THEN stdwork.stdtime ELSE 0 END) AS motbay1tot "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = "&[datesql] _
& " LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "LEFT JOIN stdworklink ON stdworklink.bookid = book.bookid "_
& "LEFT JOIN stdwork ON stdwork.stdworkid = stdworklink.stdworkid "_
& "WHERE mot.dayid ="&[motdaysql] _
& " AND ((mot.bayid = 1 AND mot.baytypeid =1) or "_
& "(mot.bayid = 2 AND mot.baytypeid =1) or "_
& "(mot.bayid = 1 AND mot.baytypeid =2) or "_
& "(mot.bayid = 2 AND mot.baytypeid =2) or "_
& "(mot.bayid = 3 AND mot.baytypeid =3)) "_
& "GROUP BY mot.motid, mot.mott, mot.bayid, mot.baytypeid, stdwork.stdtime "_
& "ORDER BY mot.motid, mot.mott, mot.bayid, mot.baytypeid, stdwork.stdtime"

mot
motid-mott-dayid-bayid-baytypeid
1 9:00 1 1 1
2 10:00 1 1 1
3 11:00 1 1 1
4 9:15 1 2 1
5 10:15 1 2 1
6 11:15 1 2 1...

book
bookid-bookdate-motid-carid
1 2006-08-01 1 1
2 2006-08-01 2 2
3 2006-08-01 3 3
4 2006-08-01 6 4...

baytype
baytypeid-baytype
1 mot
2 wait
3 standard

car
carid-reg-cusid
1 HJ06TYG 2
2 HJ55YHT 3
3 HY06TYH 4
4 HV05UYH 7

example output
date 01-08-2006
mot bay1 workhours
1 9:00 HJ06TYG 1
2 10:00 HJ55YHT 2
3 11:00 HY06TYH 1
Bay 1 Total 4

mot bay2
4 9:15 HV05UYH 2
5 10:15 ...
6 11:15 ...

Bay 2 Total 2

MOT TOTAL 6

hope this make sense