Sunday, April 24, 2011

Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Hi All,

Few days back I was stuck a bit with a SQL problem, I got it resolved with the suggestion of one of my colleagues(Anup). I have to access record  from SQL SERVER 2008 based on an aggregate function. But I got error “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Structure of my table was somewhat like below:


What I wanted from this table was records per UserId where CreatedDate is equal to minimum CreatedDate. So first I framed a query as below:

SELECT UserID, MIN(CreatedDate)
FROM RenewalHistory
Group By UserID
           (QUERY  1)
This worked fine giving me the expected results and then I added column SubscriptionType to select list, making my query like below:

SELECT UserID, MIN(CreatedDate), SubscriptionType
FROM RenewalHistory
Group By UserID
           (QUERY  2)
This started giving me error saying Column 'RenewalHistory.SubscriptionType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

To overcome this, I used table produced in query 1 as a sub-table and reused it to get desired record as shown in below query:

SELECT rh.Id, rh.UserId, rh.SubscriptionType, rh.Createddate
FROM RenewalHistory rh, (
      SELECT UserId, MIN(CreatedDate) AS CreatedDate
      FROM RenewalHistory
      Group By UserID) subRH
WHERE rh.UserId = subRH.UserId AND
rh.CreatedDate = subRH.CreatedDate
           (QUERY  3)

This was how I got my problem solved. But I feel this is not the best way to sort out this problem, so if you have some better solution for this problem. Then kindly share it here.

Saturday, April 23, 2011

Horizontal Sidebar in ASP.NET Wizard Control

Hi All,

Below is a simple yet very powerful hack to make ASP.NET Wizard control do what it should also be able to do but isn’t. That is the “horizontal sidebar for wizard steps”.

So here are the steps:

  • Add an ASP.NET wizard control to a page or user control and add wizard steps to it as per your requirement. It will look some what like below:
Default Appearance of Wizard Control
  • Now add side bar template to this control, like I did in below code snippet.
<SideBarTemplate>
<asp:DataList runat="server" ID="SideBarList" HorizontalAlign="Justify" RepeatDirection="Horizontal">
            <ItemTemplate>
                  <div id="divWizardSampleSidebar" runat="server" class="div-WizardSample-Sidebar-Steps">
                        <asp:LinkButton runat="server" ID="SideBarButton" Enabled="true" Font-Bold="true" />
div>
ItemTemplate>
asp:DataList>
                </tr><tr>
SideBarTemplate>

  • In above code lines, everything is normal, except the last second line that is highlighted in green color. This line is the one that is creating magic. After using it, wizard will look like below:

After Using Hack
 Actually, ASP.NET wizard control renders sidebar with steps and controls in two different "td" tags under same row and here I just injected a pair of opening and closing "tr" between those to make two separate rows for steps and controls. This forced wizard control to render separate rows for both steps and contents.

Now apply some css and make your wizard look a bit more appealing.

If you have better steps to make horizontal sidebar in ASP.NET wizard control then kindly let me know.

About Me

My photo
Delhi, India
Fun, music, travel and nature loving, always smiling, computer addict!!