|
Hi , I've got in my table a column with year of birth. I'm trying to do just some basic chart with age of the users. I mean I'd like to have it f.e. like this : 0-12years old, 13-17years old, etc.
How can I group integers like this ? I's there any helpful function or do I have to code it on my own ? (Let's forget about the year of birth in column and let's say that i've got already there age of users )
hope someone could help me.. ;/ | | mt09 | It is preferrable to group your datain the query, so that you don't have to get all the data on the client for processing.If there is not that much data in your scenario, then than you can write your own code to do the chart groupping. Chart does not provide that kind of functionality. Alex.
http://blogs.msdn.com/alexgor- Marked As Answer bymt09 Monday, September 21, 2009 11:58 AM
- Proposed As Answer byAlex GorevMSFT, OwnerSunday, September 20, 2009 4:36 PM
-
| | Alex Gorev | It is preferrable to group your datain the query, so that you don't have to get all the data on the client for processing.If there is not that much data in your scenario, then than you can write your own code to do the chart groupping. Chart does not provide that kind of functionality. Alex.
http://blogs.msdn.com/alexgor- Marked As Answer bymt09 Monday, September 21, 2009 11:58 AM
- Proposed As Answer byAlex GorevMSFT, OwnerSunday, September 20, 2009 4:36 PM
-
| | Alex Gorev | Thanks a lot for an answer - could You perhaps provide me with some links /ecamples how to group it like this with SQL query ?
[edit] got it, thanks | | mt09 | Perhaps it's not working like it should ;// Here's my query :
SELECT DataUrodzenia, dgvCol = CASE WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >=0 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE())<=9 THEN '0-9' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 10 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=15 THEN '10-15' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 16 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=20 THEN '16-20' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 21 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=25 THEN '21-25' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 26 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=30 THEN '26-30' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 31 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=35 THEN '31-35' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 36 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=40 THEN '36-40' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 41 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=45 THEN '41-45' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 46 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=50 THEN '46-50' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >=51 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=55 THEN '51-55' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >=56 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=60 THEN '56-60' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >=61 THEN '60+' End, COUNT(DataUrodzenia ) AS Ilosc FROM kar," & ComboBox1.SelectedValue & " WHERE kar.NumerKarty = " & ComboBox1.SelectedValue & ".NrKarty GROUP BY DataUrodzenia ORDER BY DataUrodzenia"
And here's a code i'm using for text columns grouping and it's working properly :
Chart5.Legends.Clear()
Chart5.ChartAreas.Clear()
Chart5.Series.Clear()
'Create a chartarea
Dim area5 As New ChartArea("AREA")
'To have a percentage on the y-axis
area5.AxisY.Minimum = 0
area5.AxisY.Maximum = 100
Chart5.ChartAreas.Add(area5)
'Create a series
Dim series5 As New Series("SERIES")
series5.ChartArea = "AREA"
'It won't be a real histogram, since it doesn't group any x-values, there won't be
'any empty points on the x-axis and the y-axis won't indicate the Ilosc of names.
'You most likely don't need a histogram here anyway.
'So, we just use the column type.
series5.ChartType = SeriesChartType.Column
'To show the label on top of the column.
series5.IsValueShownAsLabel = True
series5.LabelFormat = "{0:0}%"
Chart5.Series.Add(series5)
Chart5.Legends.Add("legenda1")
' Set Doughnut chart type
Chart5.Series("SERIES").ChartType = SeriesChartType.Doughnut
' Set labels style
Chart5.Series("SERIES")("PieLabelStyle") = "Inside"
' Set Doughnut radius percentage
Chart5.Series("SERIES")("DoughnutRadius") = "70"
' Explode data point with label "Italy"
' Chart1.Series("SERIES").Points(5)("Exploded") = "true"
'
' Enable 5D
Chart5.ChartAreas("AREA").Area3DStyle.Enable3D = True
' Set drawing style
Chart5.Series("SERIES")("PieDrawingStyle") = "SoftEdge"
Chart5.Legends("legenda1").IsDockedInsideChartArea = True
Chart5.Series("SERIES").IsValueShownAsLabel = True
'You probably have a query looking something like this.
Dim sql5 As String = "SELECT DataUrodzenia, dgvCol = CASE WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >=0 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE())<=9 THEN '0-9' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 10 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=15 THEN '10-15' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 16 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=20 THEN '16-20' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 21 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=25 THEN '21-25' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 26 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=30 THEN '26-30' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 31 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=35 THEN '31-35' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 36 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=40 THEN '36-40' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 41 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=45 THEN '41-45' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >= 46 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=50 THEN '46-50' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >=51 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=55 THEN '51-55' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >=56 AND DATEDIFF(YEAR, DataUrodzenia,GETDATE()) <=60 THEN '56-60' WHEN DATEDIFF(YEAR, DataUrodzenia,GETDATE()) >=61 THEN '60+' End, COUNT(DataUrodzenia ) AS Ilosc FROM kar," & ComboBox1.SelectedValue & " WHERE kar.NumerKarty = " & ComboBox1.SelectedValue & ".NrKarty GROUP BY DataUrodzenia ORDER BY DataUrodzenia"
'getDataToDataTable is just some function (that you have to implemet)
'that returns a datatable with the selected data.
'Use some other datatype if you don't want to use a datatable.
Dim dTable5 As DataTable = getDataToDataTable5(sql5)
'Find the total Ilosc of names in your data
Dim total5 As Integer = 0
For Each row As DataRow In dTable5.Rows
total5 += CInt(row("Ilosc"))
Next
Dim percentOfTotal5 As Double
For Each row As DataRow In dTable5.Rows
'You could also bind the data to the chart, but this way you can manipulate the data
'in a straightforward way and might get some sense of what's happening.
percentOfTotal5 = row("Ilosc") / total5 * 100
series5.Points.AddXY(row("DataUrodzenia"), percentOfTotal5)
Next
Private Function getDataToDataTable5(ByVal sql5 As String) As DataTable
'This is just to get example data.
'Normally you'd connect to your database and fetch the data from there.
Dim ds6 As New DataSet
Dim da6 As New SqlDataAdapter(sql5, cs)
da6.Fill(ds6, "kar")
Dim dtable5 As DataTable = New DataTable
dtable5 = ds6.Tables("kar")
'dtable.Columns.Add("Ilosc")
Return dtable5
End Function
I'm getting a result like - for every row instad of birthdate stands the group name, but it's not counted together - 2.e i've got 3 users with age between (20,30) but they are counted like 3 diftrent values (it looks like it's still counting just the birthdates ) - any ideas ?;/ | | mt09 | Lol.. that piece of code looks familiar :)
Good to know it's been of use to someone at least.. even though the comments seem a little out of place here and there is some redundant code lying around.
So anyway.. your sql-query seems faulty. You need to GROUP BY the whole CASE -statement, instead of just "DataUrodzenia"
GROUP BY
CASE WHEN DATEDIFF(YEAR, ...
... THEN '60+' END
| | sipla |
|
index > Chart Controls for .NET Framework > Integers grouping