Solved

MYSQL Expert Required - HELP

Posted on 2011-03-09
8
595 Views
Last Modified: 2012-05-11
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(sqldate)= 2002, sqlvalue, NULL)) AS UNSIGNED) AS Y2002,
CAST(GROUP_CONCAT(IF(YEAR(sqldate)= 2003, sqlvalue, NULL)) AS UNSIGNED) AS Y2003,
CAST(GROUP_CONCAT(IF(YEAR(sqldate)= 2004, sqlvalue, NULL)) AS UNSIGNED) AS Y2004,
CAST(GROUP_CONCAT(IF(YEAR(sqldate)= 2005, sqlvalue, NULL)) AS UNSIGNED) AS Y2005,
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.
0
Comment
Question by:DPP2011
8 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35089310
Can you provide the sample data from your table?

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  
0
 

Author Comment

by:DPP2011
ID: 35089796
     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  

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35090599
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.
0
 

Author Comment

by:DPP2011
ID: 35090767
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.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35095614
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
0
 

Author Comment

by:DPP2011
ID: 35099753
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.
0
 
LVL 10

Accepted Solution

by:
Mathiyazhagan earned 500 total points
ID: 35107011
Hi,

Change the query to show the order as for your list
SELECT stat_number,
CAST(GROUP_CONCAT(IF(YEAR(sqldate)= 2002, sqlvalue, NULL)) AS UNSIGNED) AS Y2002,
CAST(GROUP_CONCAT(IF(YEAR(sqldate)= 2003, sqlvalue, NULL)) AS UNSIGNED) AS Y2003,
CAST(GROUP_CONCAT(IF(YEAR(sqldate)= 2004, sqlvalue, NULL)) AS UNSIGNED) AS Y2004,
CAST(GROUP_CONCAT(IF(YEAR(sqldate)= 2005, sqlvalue, NULL)) AS UNSIGNED) AS Y2005,
WEEKOFYEAR(sqldate) AS `WEEKOFYEAR(sqldate)`
FROM test_date
WHERE  (sqldate >='2002-01-01' AND sqldate <'2005-12-31')
GROUP BY  WEEKOFYEAR(sqldate)
ORDER BY WEEKOFYEAR(sqldate)

Open in new window

Using Weekofyear mysql function.
0
 

Author Closing Comment

by:DPP2011
ID: 35112715
Hello Mathiyazhagan,

I was so close and at the same time so far.

Thank you, It works great.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
The viewer will learn how to count occurrences of each item in an array.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now