Oracle Count with Function

Question

I have a SQL like this.

SELECT A.HESAP_NO, A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF
MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, A.DATE) AS KV
FROM S_TEKLIF A

When i want calculate MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, A.DATE)/COUNT(@TEKLIF) but it doesn't work.

I use Oracle.

How can i divide in Oracle like that?

Here is my FULL aspx code;

<%@ Page Language="C#" AutoEventWireup="true"   %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Configuration" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Linq" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Data.OracleClient" %>

<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {

        Calculate.Visible = false;

    }

    protected void Calculate_Click(object sender, EventArgs e)
    {
        Calculate.Visible = true;


       double sumMV = 0;
        foreach (GridViewRow gvr in GridView1.Rows)
        {
            CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField2");
            if (cb.Checked == true)
            {
                    double amount = Convert.ToDouble(gvr.Cells[9].Text);
                    sumMV += amount;

            }
        }

        GridView1.FooterRow.Cells[9].Text = String.Format("{0:n}", sumMV);

        double sumRISK = 0;
        foreach (GridViewRow gvr in GridView1.Rows)
        {
            CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField1");
            if (cb.Checked == true)
            {
                    double amount = Convert.ToDouble(gvr.Cells[7].Text);
                    sumRISK += amount;
            }
        }

        GridView1.FooterRow.Cells[7].Text = String.Format("{0:n}", sumRISK);

        double sumKV = 0;
        foreach (GridViewRow gvr in GridView1.Rows)
        {
            CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField3");
            if (cb.Checked == true)
            {
                double amount = Convert.ToDouble(gvr.Cells[11].Text);
                if (amount != -1)
                {
                    sumKV += amount;
                }
            }
        }
        GridView1.FooterRow.Cells[11].Text = String.Format("{0:n}", sumKV);

    }
    protected void SendToGridview_Click(object sender, EventArgs e)
    {
        DateTime dt_stb;

        Calculate.Visible = true;

        string strQuery = string.Empty;

        string ConnectionString = ConfigurationManager.ConnectionStrings["ora"].ConnectionString;

        OracleConnection myConnection = new OracleConnection(ConnectionString);

        string txtBoxText1 = ((TextBox)Page.FindControl("TextBox1")).Text;
        if (txtBoxText1 != "")
        {
            strQuery = @"SELECT A.HESAP_NO, A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF, A.MUS_K_ISIM AS MUSTERI, 
                    B.MARKA, C.SASI_NO, C.SASI_DURUM, D.TAS_MAR, NVL(RISK_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS RISK,
                    NVL(MV_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.SIRA_NO, C.URUN_SIRA_NO, :S_TARIH_B),0) AS MV,
                    MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, :S_TARIH_B)/COUNT(*) OVER() AS KV
                    FROM S_TEKLIF A,  S_URUN B, S_URUN_DETAY C, KOC_KTMAR_PR D
                    WHERE A.TEKLIF_NO1 || A.TEKLIF_NO2 = B.TEKLIF_NO1 || B.TEKLIF_NO2
                    AND A.TEKLIF_NO1 || A.TEKLIF_NO2 = C.TEKLIF_NO1 || C.TEKLIF_NO2
                    AND B.SIRA_NO = C.URUN_SIRA_NO
                    AND C.SASI_DURUM IN ('A','R')
                    AND B.DISTRIBUTOR = D.DIST_KOD
                    AND B.MARKA = D.MARKA_KOD
                    AND B.URUN_KOD = D.TAS_KOD ";

        }
        string param = "";
        foreach (ListItem l in CheckBoxList1.Items)
        {
            if (l.Selected)
            {
                param += string.Format("'{0}'", l.Value);
                param += ",";
            }
        }

        try
        {
            param = param.Remove(param.Length - 1);

            strQuery = strQuery + " AND A.HESAP_NO IN (" + param + ")";

            OracleCommand myCommand = new OracleCommand(strQuery, myConnection);
            myCommand.CommandType = System.Data.CommandType.Text;
            myCommand.Connection = myConnection;

            myCommand.CommandText = strQuery;
            dt_stb = DateTime.Parse(txtBoxText1);
            myCommand.Parameters.AddWithValue(":S_TARIH_B", dt_stb);

            myConnection.Open();

            OracleDataReader dr = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            GridView1.DataSource = dr;
            GridView1.DataBind();
            GridView1.Visible = true;

            myConnection.Close();
        }
        catch
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('Choose at least one customer!')", true);
            Calculate.Visible = false;
            GridView1.Visible = false;
            TextBox1.Text = string.Empty;
        }

            double sumMV = 0;
            foreach (GridViewRow gvr in GridView1.Rows)
            {
                CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField2");
                if (cb.Checked == true)
                {
                    double amountMV = Convert.ToDouble(gvr.Cells[9].Text);


                        sumMV += amountMV;

                }
            }

            GridView1.FooterRow.Cells[9].Text = String.Format("{0:n}", sumMV);

            double sumRISK = 0;
            foreach (GridViewRow gvr in GridView1.Rows)
            {
                CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField1");
                if (cb.Checked == true)
                {
                    double amountBV = Convert.ToDouble(gvr.Cells[7].Text);


                        sumRISK += amountBV;

                }
            }

            GridView1.FooterRow.Cells[7].Text = String.Format("{0:n}", sumRISK);

            double sumKV = 0;
            foreach (GridViewRow gvr in GridView1.Rows)
            {
                CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField3");
                if (cb.Checked == true)
                {
                    double amountKV = Convert.ToDouble(gvr.Cells[11].Text);
                    if (amountKV != -1)
                    {
                        sumKV += amountKV;
                    }
                }
            }

            GridView1.FooterRow.Cells[11].Text = String.Format("{0:n}", sumKV);


    }
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">

        #form1
        {
            height: 729px;
            width: 1083px;
        }

        .style1
        {
            width: 265px;
        }

      </style>
</head>
<body>
    <form id="form1" runat="server">
    <br />
    <img src="../images/Scania_Logo.gif" style="height: 49px; width: 193px" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="X-Large" 
        ForeColor="Blue" Height="40px" Text="BV &amp; RISK SIMULATOR" 
        Width="329px" style="text-align: center"></asp:Label>
&nbsp;
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <br />
    <div style="OVERFLOW-Y:scroll; WIDTH:362px; HEIGHT:177px">
        <br />

    <table border="5" bordercolor=blue style="height: 116px; width: 343px">
    <tr>
    <td class="style1">
        <asp:CheckBoxList ID="CheckBoxList1" runat="server" 
            DataSourceID="ChechkBoxDataSource" DataTextField="MUS_K_ISIM" 
            DataValueField="HESAP_NO" Font-Size="12pt">
        </asp:CheckBoxList>
        </td>
        </tr>
    </table>
    </div>
    <div style="width: 331px">
        <br />
        <asp:Textbox ID="TextBox1" runat="server" Font-Size="X-Small" Height="13px" Font-Names="Verdana" Width="75px" ></asp:Textbox>   
        <asp:CalendarExtender Format="dd/MM/yyyy" ID="TextBox1_CalendarExtender" runat="server" 
            TargetControlID="TextBox1">
        </asp:CalendarExtender>
        <asp:Image ID="ImageButton3" runat="server"  ImageUrl="~/images/SmallCalendar.gif"/>
        <br />
       </div>
        <asp:SqlDataSource ID="ChechkBoxDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ora %>" 
            ProviderName="<%$ ConnectionStrings:ora.ProviderName %>" 
            SelectCommand="SELECT DISTINCT(A.HESAP_NO),A.MUS_K_ISIM
FROM S_TEKLIF A
ORDER BY A.MUS_K_ISIM">
        </asp:SqlDataSource>
    <br />
    <asp:Button ID="SendToGridview" runat="server" Text="Calculate" Width="73px" 
        onclick="SendToGridview_Click" />
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server"  
         Width="16px" CellPadding="4" 
        GridLines="None" Height="16px" ForeColor="#333333" 
        AutoGenerateColumns="False" DataKeyNames="RISK,MV" BorderColor="White" 
        BorderStyle="Ridge" ShowFooter="True" >
        <AlternatingRowStyle BackColor="White" />
        <Columns>
        <asp:BoundField HeaderText="HESAP" DataField="HESAP_NO" />
     <asp:BoundField HeaderText="TEKLIF" DataField="TEKLIF" >
            <ItemStyle Wrap="False" />
            </asp:BoundField>
     <asp:BoundField HeaderText="MUSTERI" DataField="MUSTERI" >
            <ItemStyle Wrap="False" />
            </asp:BoundField>
     <asp:BoundField HeaderText="MARKA" DataField="MARKA" />
     <asp:BoundField HeaderText="SASI" DataField="SASI_NO" >
            <ItemStyle Wrap="False" />
            </asp:BoundField>
     <asp:BoundField HeaderText="DURUM" DataField="SASI_DURUM" />
     <asp:BoundField HeaderText="TASIT MARKA" DataField="TAS_MAR" >
            <ItemStyle Wrap="False" />
            </asp:BoundField>
     <asp:BoundField HeaderText="BV" DataField="RISK" DataFormatString="{0:n2}"/>


            <asp:templatefield headertext="">
            <itemtemplate>
            <asp:CheckBox DataField="NameCheckBoxField1" ID="NameCheckBoxField1" Checked="True" runat="server"></asp:CheckBox>
            </itemtemplate>
            </asp:templatefield>
            <asp:BoundField HeaderText="MV" DataField="MV" DataFormatString="{0:n2}"/>
            <asp:templatefield headertext="">
            <itemtemplate>
            <asp:CheckBox DataField="NameCheckBoxField2" ID="NameCheckBoxField2" Checked="True" runat="server"></asp:CheckBox>
            </itemtemplate>
            </asp:templatefield>
            <asp:BoundField HeaderText="KV" DataField="KV" DataFormatString="{0:n2}"/>
            <asp:templatefield headertext="">
            <itemtemplate>
            <asp:CheckBox DataField="NameCheckBoxField3" ID="NameCheckBoxField3" Checked="True" runat="server"></asp:CheckBox>
            </itemtemplate>
            </asp:templatefield>

        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" ForeColor="White" Font-Bold="True" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <sortedascendingcellstyle backcolor="#F4F4FD" />
        <sortedascendingheaderstyle backcolor="#5A4C9D" />
        <sorteddescendingcellstyle backcolor="#D8D8F0" />
        <sorteddescendingheaderstyle backcolor="#3E3277" />

<SortedAscendingCellStyle BackColor="#F5F7FB"></SortedAscendingCellStyle>

<SortedAscendingHeaderStyle BackColor="#6D95E1"></SortedAscendingHeaderStyle>

<SortedDescendingCellStyle BackColor="#E9EBEF"></SortedDescendingCellStyle>

<SortedDescendingHeaderStyle BackColor="#4870BE"></SortedDescendingHeaderStyle>
  </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ora %>" 
        ProviderName="<%$ ConnectionStrings:ora.ProviderName %>" 

        SelectCommand="  SELECT A.HESAP_NO, A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF, A.MUS_K_ISIM , 
B.MARKA, C.SASI_NO, C.SASI_DURUM, D.TAS_MAR, NVL(RISK_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS RISK,
NVL(MV_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS MV,
MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, :S_TARIH_B)/COUNT(*) OVER() AS KV, 'NameCheckBoxField1' = 0x1, 'NameCheckBoxField2' = 0x1, 'NameCheckBoxField3' = 0x1
FROM S_TEKLIF A,  S_URUN B, S_URUN_DETAY C, KOC_KTMAR_PR D
WHERE A.TEKLIF_NO1 || A.TEKLIF_NO2 = B.TEKLIF_NO1 || B.TEKLIF_NO2
AND A.TEKLIF_NO1 || A.TEKLIF_NO2 = C.TEKLIF_NO1 || C.TEKLIF_NO2
AND B.SIRA_NO = C.URUN_SIRA_NO
AND C.SASI_DURUM IN ('A','R')
AND B.DISTRIBUTOR = D.DIST_KOD
AND B.MARKA = D.MARKA_KOD
AND B.URUN_KOD = D.TAS_KOD ">
    </asp:SqlDataSource>
    <br />
    <asp:Button ID="Calculate" runat="server" onclick="Calculate_Click" 
        Text="Calculate" />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <br />
    <br />
    </form>
</body>
</html>

What is @TEKLIF supposed to be? This is not a valid Oracle column name. — user330315
– user330315, Commented Mar 25, 2011 at 15:09

Rob van Wijk · Accepted Answer · 2011-03-25 15:20:23Z

5

Obviously untested, but it should be something like:

SELECT A.HESAP_NO
     , A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF
     , MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, A.DATE) / count(*) over () AS KV
  FROM S_TEKLIF A

Explanation: if you want to use count(*), you'd have to aggregate in your query, but you're not doing that. So instead you can use an analytic function count(*) over () which counts the same number for each row.

Hope this helps.

Regards,
Rob.

answered Mar 25, 2011 at 15:20

Rob van Wijk

17.7k5 gold badges41 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Soner Gönül Over a year ago

COUNT() OVER() is getting all rows count in gridview. For example this picture i1103.hizliresim.com/2011/3/28/2658.png TEKLIF count is 4 (S06-2/343) But Count() Over() divide 5.

Rob van Wijk Over a year ago

I can't see that picture, but if I understand correctly, then a change from count(*) to count(a.teklif_no1) will count all non null occurrences from teklif_no1, which whill hopefully be 4 in your situation. EDIT: Just read Michael's answer and he might be right. The better you specify your question, the better the answers ...

Soner Gönül Over a year ago

I found the solution. I change over (partition by TEKLIF) to OVER(partition by A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2).. And it works! Thanks !

Michael Broughton · Accepted Answer · 2011-03-25 19:14:19Z

1

I think you need to explain better what you are counting in your example. Your query shows values that get returned for each row, then you want to divide by "COUNT(@TEKLIF)"

is that notation meant to be the count of distinct TEKLIFs? Or the count of rows for the TEKLIF in this row? Or the count of all rows in the table?

e.g.)

SELECT   A.HESAP_NO      
       , A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF      
       , MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, A.DATE) / count(distinct TEFLIK) over () AS KV   
FROM S_TEKLIF A

or

SELECT   A.HESAP_NO      
       , A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF      
       , MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, A.DATE) / count(*) over (partition by TEFLIK) AS KV   
FROM S_TEKLIF A

or Rob's solution

Hope that one of those helps.

answered Mar 25, 2011 at 19:14

Michael Broughton

4,05516 silver badges12 bronze badges

2 Comments

Soner Gönül Over a year ago

Do i have to use Group By for KV?

Rob van Wijk Over a year ago

Your ideas might be right, but that syntax won't work: teflik is an alias, not a column.

Collectives™ on Stack Overflow

Oracle Count with Function

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related