kneeki kneeki - 1 month ago 17
C# Question

Using SUM(Total). SQL statement works fine in PHP+MySQL

I'm attempting to re-use some mySQL in my Visual Studio 2015 C# project. I'm attempting to retrieve an output from the database which sums up the sales and total sale volume for each salesman. When using the mySQL statement that works great in my PHP project, I'm given the error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.


AGENT structure:

CREATE TABLE [dbo].[AGENT] (
[AgentID] INT IDENTITY (1, 1) NOT NULL,
[AgentName] TEXT NOT NULL,
[OfficeKey] INT NOT NULL,
PRIMARY KEY CLUSTERED ([AgentID] ASC)
);


OFFICE structure:

CREATE TABLE [dbo].[OFFICE] (
[OfficeID] INT IDENTITY (1, 1) NOT NULL,
[OfficeLocation] NCHAR (20) NOT NULL,
PRIMARY KEY CLUSTERED ([OfficeID] ASC)
);


SALE structure:

CREATE TABLE [dbo].[SALE] (
[SaleID] INT IDENTITY (1, 1) NOT NULL,
[SaleDate] DATE NOT NULL,
[AgentKey] INT NOT NULL,
[Amount] MONEY NOT NULL,
[DestinationKey] INT NOT NULL,
PRIMARY KEY CLUSTERED ([SaleID] ASC)
);


Here is my source:

namespace Desktop_Campus_Travel
{
public partial class Agent_Bookings : Form
{
public Agent_Bookings()
{
InitializeComponent();
}

private void Agent_Bookings_Load(object sender, EventArgs e)
{
List<AgentBooking> agentList = new List<AgentBooking>();
SqlConnection conn = Database.GetConnection();

string selStmt = @"
SELECT AGENT.AgentName,
OFFICE.OfficeLocation,
COUNT(AGENT.AgentID) AS Sales,
SUM(SALE.Amount) AS Total
FROM AGENT
JOIN OFFICE
ON AGENT.OfficeKey = OFFICE.OfficeID
JOIN SALE
ON SALE.AgentKey = AGENT.AgentID
GROUP BY AGENT.AgentName";

SqlCommand selCmd = new SqlCommand(selStmt, conn);
try
{
conn.Open();
SqlDataReader reader = selCmd.ExecuteReader();
while (reader.Read())
{
AgentBooking agent = new AgentBooking();
agent.AgentName = reader["AgentName"].ToString();
agent.OfficeLocation = reader["OfficeLocation"].ToString();
agent.Sales = reader["Sales"].ToString();
agent.Total = reader["Total"].ToString();

agentList.Add(agent);
}
reader.Close();
}
catch (SqlException ex) { throw ex; }
finally { conn.Close(); }
}

private void button1_Click(object sender, EventArgs e)
{
this.Close();
}
}
}

Answer

Step 1

Change your Agent Table to

CREATE TABLE [dbo].[AGENT] (
    [AgentID]   INT  IDENTITY (1, 1) NOT NULL,
    [AgentName] NVARCHAR(100) NOT NULL,
    [OfficeKey] INT  NOT NULL,
    PRIMARY KEY CLUSTERED ([AgentID] ASC)
);

SQL Server is not happy to GROUP BY TEXT data type.

Step 2

Change your query to

SELECT
    AGENT.AgentName, 
    OFFICE.OfficeLocation,
    SALE2.Sales,
    SALE2.Total
FROM 
    AGENT 
    JOIN OFFICE ON AGENT.OfficeKey = OFFICE.OfficeID 
    JOIN
    (
        SELECT 
            SALE.AgentKey,
            COUNT(SALE.SaleID) AS Sales,
            SUM(SALE.Amount) AS Total 
        FROM   SALE 
        GROUP  BY SALE.AgentKey
    ) SALE2 ON SALE2.AgentKey = AGENT.AgentID 

SQL Server is also not happy to select column that is not contained in either an aggregate function or the GROUP BY clause when GROUP BY are used.

You can do only Step 2 without doing Step 1 and it will work, but Text datatype is deprecated (MSDN Reference) so you shouldn't use it.

Comments