How do I flatten this data out to one row?

Question

How do I flatten this data out to one row?

kawi6rr

SSC-Addicted

Points: 403
More actions
August 18, 2016 at 1:31 pm

#104298

I’m totally lost, I think it can be done with a union query but I can’t get it to work. Any help is greatly appreciated, thanks in advance.
You can see the data is listed on 4 lines, what I’m trying to do is get them flat on one line.
qry_Timelog_Data_Count-TEST
full_namestart_yearstart_week sundaymondaytuesdaywednesday thursday fridaysaturdaySumOfweekly_total
John Doe2016 26 .00.00 .00 .92 .00 .00 .00 0.92
John Doe2016 26 .00.00 .75 .00 .00 .00 .00 0.75
John Doe2016 27 .00.92 .00 .00 .00 .00 .00 0.92
John Doe2016 27 .92.00 .00 .00 .00 .00 .00 0.92
SELECT SUM(cnt) FROM
(
SELECT COUNT(*) AS cnt FROM [SNF_TIME] WHERE start_week in(‘’)
UNION ALL
SELECT COUNT(*) AS cnt FROM [SNF_TIME] WHERE start_week in(‘’))
AS COUNT;
Preferred results
full_namestart_yearstart_week sunday monday tuesday wednesday thursday friday saturdaySumOfweekly_total
John Doe2016 26 .92 .92 .75 .92 .00 .00 .00 3.51

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

But isn't your results from two different weeks (week 26 and week 27)? Why would you be combining them into one week? Shouldn't it be the first two rows combined and the last two rows combined?

-SQLBill

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

kawi6rr (8/18/2016)
I’m totally lost, I think it can be done with a union query but I can’t get it to work. Any help is greatly appreciated, thanks in advance.
You can see the data is listed on 4 lines, what I’m trying to do is get them flat on one line.
qry_Timelog_Data_Count-TEST
full_namestart_yearstart_week sundaymondaytuesdaywednesday thursday fridaysaturdaySumOfweekly_total
John Doe2016 26 .00.00 .00 .92 .00 .00 .00 0.92
John Doe2016 26 .00.00 .75 .00 .00 .00 .00 0.75
John Doe2016 27 .00.92 .00 .00 .00 .00 .00 0.92
John Doe2016 27 .92.00 .00 .00 .00 .00 .00 0.92
SELECT SUM(cnt) FROM
(
SELECT COUNT(*) AS cnt FROM [SNF_TIME] WHERE start_week in(‘’)
UNION ALL
SELECT COUNT(*) AS cnt FROM [SNF_TIME] WHERE start_week in(‘’))
AS COUNT;
Preferred results
full_namestart_yearstart_week sunday monday tuesday wednesday thursday friday saturdaySumOfweekly_total
John Doe2016 26 .92 .92 .75 .92 .00 .00 .00 3.51

What about something like this?

SELECT full_name,

start_year,

MIN(start_week) AS start_week,

SUM(sunday) AS sunday,

SUM(monday) AS monday,

SUM(tuesday) AS Tuesday

FROM [SNF_TIME]

WHERE start_week in(26,27))

GROUP BY full_name, start_year;

Why would you need the UNION ALL when you can include both weeks in the same statement?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kawi6rr SSC-Addicted Points: 403 More actions · Answer 3

I'm sorry I missed that there were 2 weeks in the data, I would not want to combined them. Thanks for your reply I'll try the idea you gave above, thanks.