How to add new column in select statement

Question

i want to add new column in my select statement

SELECT name,line,style,operation,
7to8am,8to9am,9to10am,10to11am,11to12am,
1to2pm,2to3pm,3to4pm,4to5pm,5to6pm,6to7pm,7to8pm,8to9pm,9to10pm,10to11pm,11to12pm,
sum(7to8am+8to9am+9to10am+10to11am+11to12am+1to2pm
+2to3pm+3to4pm+4to5pm+5to6pm+6to7pm+7to8pm+
8to9pm+9to10pm+10to11pm+11to12pm) as DailyTotal,id from new_hourly GROUP By line

i want to add a new column that will show sum of DailyTotal that the day is today

This is my sql backup file http://www.uploadmb.com/dw.php?id=1446536983

Please help me! thank you so much!

Maybe. It won't help much. But I can't say much more without seeing a sample data set. Don't give us something to download. Just provide CREATE and INSERT statements (enough to be representative) right here in an edit to your question (and/or an SQL fidddle) — Strawberry, Nov 03 '15 at 09:31

score 1 · Accepted Answer · answered Nov 03 '15 at 08:12

1

So you want one additional columns that adds only lines where datee equals current date?

I'd go for case-when expression in this case:

coalesce(
      sum(
        case 
          when datee = CURDATE() 
             then 7to8am+8to9am+9to10am+10to11am+11to12am+1to2pm+2to3pm+3to4pm+4to5pm+5to6pm+6to7pm+7to8pm+8to9pm+9to10pm+10to11pm+11to12pm 
             else null 
          end
         )
    ,0) as TodaysTotal

That's summing up only those rows where column datee is curdate() and returning 0 if no rows at all are present for today.

Full SQL:

SELECT name,line,style,operation,
7to8am,8to9am,9to10am,10to11am,11to12am,
1to2pm,2to3pm,3to4pm,4to5pm,5to6pm,6to7pm,7to8pm,8to9pm,9to10pm,10to11pm,11to12pm,
sum(7to8am+8to9am+9to10am+10to11am+11to12am+1to2pm
+2to3pm+3to4pm+4to5pm+5to6pm+6to7pm+7to8pm+
8to9pm+9to10pm+10to11pm+11to12pm) as DailyTotal,

coalesce(
  sum(
    case 
      when datee = CURDATE() 
         then 7to8am+8to9am+9to10am+10to11am+11to12am+1to2pm+2to3pm+3to4pm+4to5pm+5to6pm+6to7pm+7to8pm+8to9pm+9to10pm+10to11pm+11to12pm 
         else null 
      end
     )
,0) as TodaysTotal

,id from new_hourly GROUP By line

answered Nov 03 '15 at 08:12

Jan

13,738
3
30
55

This doesn't really improve things. It still returns arbitrary results for name, style, operation, and id. – Strawberry Nov 03 '15 at 09:28
`SELECT line,style,operation,sum(7to8am) as "7-8 A.M",sum(8to9am) as "8-9 A.M",sum(9to10am) as "9-10 A.M",sum(10to11am) as "10-11 A.M",sum(11to12am) as "11-12 A.M",sum(1to2pm) as "1-2 P.M",sum(2to3pm) as "2-3 P.M",sum(3to4pm) as "3-4 P.M",sum(4to5pm) as "4-5 P.M",sum(5to6pm) as "5-6 P.M",sum(6to7pm) as "6-7 P.M",sum(7to8pm) as "7-8 P.M",sum(8to9pm) as "8-9 P.M",sum(9to10pm) as "9-10 P.M",sum(10to11pm) as "10-11 P.M",sum(11to12pm) as "11-12 P.M" from new_hourly where datee = CURDATE() GROUP BY line;` sir i wanted to add a new colum which sum all the column please – Mheljun Enanoria Nov 05 '15 at 07:35
Hi, first off you should consider grouping by style and operation as well. Apart from that you can do it just like above: Just `sum(7to8am + 8to9am + ...) totalToday` – Jan Nov 05 '15 at 08:05
but i like the total of overall not total of the current date because when i use sum( ....) it shows the total of that day not over all – Mheljun Enanoria Nov 05 '15 at 09:28

How to add new column in select statement

1 Answers1