CODEWITHSUNDEEP
sqlvb.netcount
Nimitz E.
Nimitz E.

Reputation: 95

How to count sales in database via sql query vb.net

How can I count the total sales of this table

table name: sales table columns: roomnum, date, rate, agent, sales_amount

I want to count the sales column under date column.

By single date or range dates.

So far I figure out how to count the number of sales via dates

con.Open()
        sql = "SELECT COUNT(*) as 'Number of Rows' FROM sales where [date] = ?"
        cmd = New OleDb.OleDbCommand(sql, con)
        cmd.Parameters.AddWithValue("@date", Now.ToString("MM/dd/yyyy"))

        Dim count As Int16 = Convert.ToInt16(cmd.ExecuteScalar())
        check.Text = count.ToString
        con.Close()

Now I wanted to figure out how I can calculate the sales via date also.

Thanks

Upvotes: 0

Views: 1865

Answers (1)

Luc Morin
Luc Morin

Reputation: 5380

Do something like this:

con.Open()
sql = "SELECT SUM(sales_amount) as 'TotalSold' FROM sales where [date] BETWEEN ? AND ?"
cmd = New OleDb.OleDbCommand(sql, con)
cmd.Parameters.AddWithValue("@from", fromdate)
cmd.Parameters.AddWithValue("@to", todate)

Dim total As Decimal = Convert.ToDecimal(cmd.ExecuteScalar())
check.Text = total.ToString
con.Close()

Where fromdate and todate are defined as DateTime variables.

Also, I used Decimal, but I don't know the data type in the table. Adapt to your needs.

Cheers

Upvotes: 2

Related Questions