DPP2011
asked on
MYSQL Expert Required - HELP
Hello,
I need some expert to complete or fix my query, I have been on it for a while and I can't get it to work properly.
Let me explained:
I need a to have a querry that will organice my data based on the Thursday day of the year.
Required Result:
Stat_number = 61
Order by Number of Thursday of the year.
Query return:
Trusday 2002 2003 2004 2005 2006
1 13104 18981 18011 22434 25632
2 27839 21666 16844 28853 28995
3 24918 39169 23419 43432 56054
4 28338 38180 29041 16507 32486
5 24855 39511 40537 45230 50282
6 74573 52280 50595 50541 44267
7 54030 56899 28685 31733 34418
8 41726 53228 49000 52310 37909
9 48987 67906 60749 52969 56778
10 65778 39747 57164 52724 48121
Per the sample above we can see stat_number = 61
In 2002 the value of stat_number "61" for the first thursday of the year (2002) was 13104
in 2003 the value of stat_number "61" for the first thursday of the year (2003) was 18981
in 2004 the value of stat_number "61" for the first thursday of the year (2004) was 18011
in 2005 the value of stat_number "61" for the first thursday of the year (2004) was 22434
in 2006 the value of stat_number "61" for the first thursday of the year (2004) was 25632
and the same for the Second Thursday of the year and so on.
--------------------------
I have created the following query, but is not giving me the right result, I have try different queries
and search on the net for hours but nothing that I can consider helpfull.
----------
SELECT
stat_number,
sqldate,
sqlvalue,
ID,
CAST(GROUP_CONCAT(IF(YEAR(
CAST(GROUP_CONCAT(IF(YEAR(
CAST(GROUP_CONCAT(IF(YEAR(
CAST(GROUP_CONCAT(IF(YEAR(
DAYOFYEAR(sqldate) AS `DAYOFYEAR(sqldate)`
FROM statval1
WHERE (1 =1) AND (sqldate >='2002-01-01' AND sqldate <'2005-12-31')
GROUP BY sqldate
ORDER BY DAYOFYEAR(sqldate)
---------------------
My Query Result comes out like:
stat_number sqldate sqlvalue ID Y2002 Y2003 Y2004 Y2005 DAYOFYEAR(sqldate)
9 01-01-04 3630 52056 NULL NULL 3630 NULL 1
87 02-01-03 0 54067 NULL 0 NULL NULL 2
61 03-01-02 10134 54938 10134 NULL NULL NULL 3
12 06-01-05 5050 48900 NULL NULL NULL 5050 6
12 08-01-04 2570 52029 NULL NULL 2570 NULL 8
87 09-01-03 0 54040 NULL 0 NULL NULL 9
107 10-01-02 1 54932 1 NULL NULL NULL 10
12 13-01-05 4200 48818 NULL NULL NULL 4200 13
12 15-01-04 8536 51990 NULL NULL 8536 NULL 15
1 16-01-03 82.353 54009 NULL 82 NULL NULL 16
107 17-01-02 4 54926 4 NULL NULL NULL 17
12 20-01-05 7020 48735 NULL NULL NULL 7020 20
12 22-01-04 10250 51947 NULL NULL 10250 NULL 22
1 23-01-03 88.235 53978 NULL 88 NULL NULL 23
Can someone please fix it so I can get the query result that I am looking for, I will appreciate it.
I need some expert to complete or fix my query, I have been on it for a while and I can't get it to work properly.
Let me explained:
I need a to have a querry that will organice my data based on the Thursday day of the year.
Required Result:
Stat_number = 61
Order by Number of Thursday of the year.
Query return:
Trusday 2002 2003 2004 2005 2006
1 13104 18981 18011 22434 25632
2 27839 21666 16844 28853 28995
3 24918 39169 23419 43432 56054
4 28338 38180 29041 16507 32486
5 24855 39511 40537 45230 50282
6 74573 52280 50595 50541 44267
7 54030 56899 28685 31733 34418
8 41726 53228 49000 52310 37909
9 48987 67906 60749 52969 56778
10 65778 39747 57164 52724 48121
Per the sample above we can see stat_number = 61
In 2002 the value of stat_number "61" for the first thursday of the year (2002) was 13104
in 2003 the value of stat_number "61" for the first thursday of the year (2003) was 18981
in 2004 the value of stat_number "61" for the first thursday of the year (2004) was 18011
in 2005 the value of stat_number "61" for the first thursday of the year (2004) was 22434
in 2006 the value of stat_number "61" for the first thursday of the year (2004) was 25632
and the same for the Second Thursday of the year and so on.
--------------------------
I have created the following query, but is not giving me the right result, I have try different queries
and search on the net for hours but nothing that I can consider helpfull.
----------
SELECT
stat_number,
sqldate,
sqlvalue,
ID,
CAST(GROUP_CONCAT(IF(YEAR(
CAST(GROUP_CONCAT(IF(YEAR(
CAST(GROUP_CONCAT(IF(YEAR(
CAST(GROUP_CONCAT(IF(YEAR(
DAYOFYEAR(sqldate) AS `DAYOFYEAR(sqldate)`
FROM statval1
WHERE (1 =1) AND (sqldate >='2002-01-01' AND sqldate <'2005-12-31')
GROUP BY sqldate
ORDER BY DAYOFYEAR(sqldate)
---------------------
My Query Result comes out like:
stat_number sqldate sqlvalue ID Y2002 Y2003 Y2004 Y2005 DAYOFYEAR(sqldate)
9 01-01-04 3630 52056 NULL NULL 3630 NULL 1
87 02-01-03 0 54067 NULL 0 NULL NULL 2
61 03-01-02 10134 54938 10134 NULL NULL NULL 3
12 06-01-05 5050 48900 NULL NULL NULL 5050 6
12 08-01-04 2570 52029 NULL NULL 2570 NULL 8
87 09-01-03 0 54040 NULL 0 NULL NULL 9
107 10-01-02 1 54932 1 NULL NULL NULL 10
12 13-01-05 4200 48818 NULL NULL NULL 4200 13
12 15-01-04 8536 51990 NULL NULL 8536 NULL 15
1 16-01-03 82.353 54009 NULL 82 NULL NULL 16
107 17-01-02 4 54926 4 NULL NULL NULL 17
12 20-01-05 7020 48735 NULL NULL NULL 7020 20
12 22-01-04 10250 51947 NULL NULL 10250 NULL 22
1 23-01-03 88.235 53978 NULL 88 NULL NULL 23
Can someone please fix it so I can get the query result that I am looking for, I will appreciate it.
ASKER
Hello Sharath 123,
The following is a sample of the data in my table.
stat_number sqldate sqlvalue ID Post_number
706 2011-02-24 47 149 NULL
707 2011-02-24 20 150 NULL
710 2011-02-24 45 151 NULL
711 2011-02-24 49 152 NULL
23 2011-02-24 67 153 NULL
592 2011-02-24 0 154 NULL
150 2011-02-24 133 155 NULL
64 2011-02-17 10507 157 NULL
66 2011-02-17 40885 158 NULL
80 2011-02-17 191 159 NULL
65 2011-02-17 45835 160 NULL
647 2011-02-17 14 161 NULL
67 2011-02-17 4950 162 NULL
74 2011-02-17 2 163 NULL
75 2011-02-17 4 164 NULL
146 2011-02-17 50 165 NULL
149 2011-02-17 25 166 NULL
650 2011-02-17 14 167 NULL
71 2011-02-17 73.13 168 NULL
The values get entered once a week on a Thursday, so all the entries
will contain a Thursday date (sqldate).
I have over 250 stats_number which values get entered every Thursday.
Every stats-number will have only one value entered per week.
Please let me know if you need more data.
Yes,
this is your expected result. after running the query for stat_number = 61.
Trusday 2002 2003 2004 2005 2006
1 13104 18981 18011 22434 25632
2 27839 21666 16844 28853 28995
3 24918 39169 23419 43432 56054
4 28338 38180 29041 16507 32486
5 24855 39511 40537 45230 50282
6 74573 52280 50595 50541 44267
7 54030 56899 28685 31733 34418
8 41726 53228 49000 52310 37909
9 48987 67906 60749 52969 56778
10 65778 39747 57164 52724 48121
The following is a sample of the data in my table.
stat_number sqldate sqlvalue ID Post_number
706 2011-02-24 47 149 NULL
707 2011-02-24 20 150 NULL
710 2011-02-24 45 151 NULL
711 2011-02-24 49 152 NULL
23 2011-02-24 67 153 NULL
592 2011-02-24 0 154 NULL
150 2011-02-24 133 155 NULL
64 2011-02-17 10507 157 NULL
66 2011-02-17 40885 158 NULL
80 2011-02-17 191 159 NULL
65 2011-02-17 45835 160 NULL
647 2011-02-17 14 161 NULL
67 2011-02-17 4950 162 NULL
74 2011-02-17 2 163 NULL
75 2011-02-17 4 164 NULL
146 2011-02-17 50 165 NULL
149 2011-02-17 25 166 NULL
650 2011-02-17 14 167 NULL
71 2011-02-17 73.13 168 NULL
The values get entered once a week on a Thursday, so all the entries
will contain a Thursday date (sqldate).
I have over 250 stats_number which values get entered every Thursday.
Every stats-number will have only one value entered per week.
Please let me know if you need more data.
Yes,
this is your expected result. after running the query for stat_number = 61.
Trusday 2002 2003 2004 2005 2006
1 13104 18981 18011 22434 25632
2 27839 21666 16844 28853 28995
3 24918 39169 23419 43432 56054
4 28338 38180 29041 16507 32486
5 24855 39511 40537 45230 50282
6 74573 52280 50595 50541 44267
7 54030 56899 28685 31733 34418
8 41726 53228 49000 52310 37909
9 48987 67906 60749 52969 56778
10 65778 39747 57164 52724 48121
The number of Thursdays in a year are usually 52 or 53.
It seems like you might want to SELECT stat_number, sql_date FROM statval1 GROUP BY sqldate ORDER BY sqldate
Or something close to that.
It seems like you might want to SELECT stat_number, sql_date FROM statval1 GROUP BY sqldate ORDER BY sqldate
Or something close to that.
ASKER
Thank you for the reply.
Due to the way the database was originally set up I need to be able to isolate
the values of each year in a column. - Converting rows into columns.
The reason why I need to Isolate the values per year in it own column is because I need to chart
several years in one line graph, I have used other query but the data does not comes out right to be able to chart it.
If I could isolate the data per year as my query does and group by or order by Thursday day of the year it may come out the way I want it, but that is beyond my knowledge.
If anyone know another way to design the query that will result in what I want , please post it so I can test it on my system.
Due to the way the database was originally set up I need to be able to isolate
the values of each year in a column. - Converting rows into columns.
The reason why I need to Isolate the values per year in it own column is because I need to chart
several years in one line graph, I have used other query but the data does not comes out right to be able to chart it.
If I could isolate the data per year as my query does and group by or order by Thursday day of the year it may come out the way I want it, but that is beyond my knowledge.
If anyone know another way to design the query that will result in what I want , please post it so I can test it on my system.
Still trying to get a good grip on this problem. I looked at the sample data here: ID:35089796. That post also showed expected results after running the query for stat_number = 61. But I could not find any stat_number = 61 in the sample data. Would it be possible for you to show us a sample data set and a sample results set that are mutually consistent and that illustrate the work product you are trying to create? Thanks. ~Ray
ASKER
Hello Paul,
Attached you will find an CSV file of all the sample values for
stats_number 61. SAMPLEDATA.csv, The csv file contain about 9 years
worth of recors and the sample query result is only using 4 years of that
data.
The following is the expented query result for stat_number "61" for only
years 2006 to 2009.
Thursday Y-2006 Y-2007 Y-2008 Y-2009
T1 13104 18981 18011 22434
T2 27839 21666 16844 28853
T3 24918 39169 23419 43432
T4 28338 38180 29041 16507
T5 24855 39511 40537 45230
T6 74573 52280 50595 50541
T7 54030 56899 28685 31733
T8 41726 53228 49000 52310
T9 48987 67906 60749 52969
T10 65778 39747 57164 52724
T11 38034 66057 52077 67332
T12 36678 55988 79196 51375
T13 56343 44156 55550 50010
T14 66780 57674 56282 74393
T15 57024 40418 58804 32358
T16 42752 43821 68179 49684
T17 40203 36612 58239 41757
T18 50822 45610 76036 52354
T19 75990 60981 88270 50010
T20 69659 33402 55900 45474
T21 54474 44051 48490 42019
T22 76767 63066 67160 82016
T23 47492 87491 78742 41777
T24 39641 73661 54452 43712
T25 38036 45150 71215 35778
T26 42973 51975 47680 57244
T27 71615 61551 81480 57387
T28 69576 48029 65098 56562
T29 56059 45940 56484 28919
T30 45455 39926 39223 42212
T31 83330 48622 53882 71972
T32 47248 58739 67529 46007
T33 37801 50936 65677 52431
T34 45670 52470 45548 34239
T35 91136 51813 54386 80225
T36 63394 83358 55815 48654
T37 64824 60990 38317 60870
T38 62397 39295 36433 53096
T39 47380 54158 55965 48115
T40 55911 61633 69666 59316
T41 68008 36710 54818 57341
T42 43249 56494 61300 48338
T43 54287 52241 53915 38320
T44 63379 60618 67505 46882
T45 40597 54057 63082 38753
T46 57477 45441 53952 50922
T47 43262 39305 43013 42873
T48 51686 50620 33419 56478
T49 66021 48022 48892 33597
T50 38203 47079 48369 28006
T51 27611 52020 42370 50247
T52 12418 47209 46137 6279
Thank you in advance for your help.
Attached you will find an CSV file of all the sample values for
stats_number 61. SAMPLEDATA.csv, The csv file contain about 9 years
worth of recors and the sample query result is only using 4 years of that
data.
The following is the expented query result for stat_number "61" for only
years 2006 to 2009.
Thursday Y-2006 Y-2007 Y-2008 Y-2009
T1 13104 18981 18011 22434
T2 27839 21666 16844 28853
T3 24918 39169 23419 43432
T4 28338 38180 29041 16507
T5 24855 39511 40537 45230
T6 74573 52280 50595 50541
T7 54030 56899 28685 31733
T8 41726 53228 49000 52310
T9 48987 67906 60749 52969
T10 65778 39747 57164 52724
T11 38034 66057 52077 67332
T12 36678 55988 79196 51375
T13 56343 44156 55550 50010
T14 66780 57674 56282 74393
T15 57024 40418 58804 32358
T16 42752 43821 68179 49684
T17 40203 36612 58239 41757
T18 50822 45610 76036 52354
T19 75990 60981 88270 50010
T20 69659 33402 55900 45474
T21 54474 44051 48490 42019
T22 76767 63066 67160 82016
T23 47492 87491 78742 41777
T24 39641 73661 54452 43712
T25 38036 45150 71215 35778
T26 42973 51975 47680 57244
T27 71615 61551 81480 57387
T28 69576 48029 65098 56562
T29 56059 45940 56484 28919
T30 45455 39926 39223 42212
T31 83330 48622 53882 71972
T32 47248 58739 67529 46007
T33 37801 50936 65677 52431
T34 45670 52470 45548 34239
T35 91136 51813 54386 80225
T36 63394 83358 55815 48654
T37 64824 60990 38317 60870
T38 62397 39295 36433 53096
T39 47380 54158 55965 48115
T40 55911 61633 69666 59316
T41 68008 36710 54818 57341
T42 43249 56494 61300 48338
T43 54287 52241 53915 38320
T44 63379 60618 67505 46882
T45 40597 54057 63082 38753
T46 57477 45441 53952 50922
T47 43262 39305 43013 42873
T48 51686 50620 33419 56478
T49 66021 48022 48892 33597
T50 38203 47079 48369 28006
T51 27611 52020 42370 50247
T52 12418 47209 46137 6279
Thank you in advance for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Mathiyazhagan,
I was so close and at the same time so far.
Thank you, It works great.
I was so close and at the same time so far.
Thank you, It works great.
Is this your expected result?
Trusday 2002 2003 2004 2005 2006
1 13104 18981 18011 22434 25632
2 27839 21666 16844 28853 28995
3 24918 39169 23419 43432 56054
4 28338 38180 29041 16507 32486
5 24855 39511 40537 45230 50282
6 74573 52280 50595 50541 44267
7 54030 56899 28685 31733 34418
8 41726 53228 49000 52310 37909
9 48987 67906 60749 52969 56778
10 65778 39747 57164 52724 48121