CODEWITHSUNDEEP
c#asp.netsqlselectcount
Kevin Jensen Petersen
Kevin Jensen Petersen

Reputation: 513

Counting SQL results aswell as outputting the result data

What i try to do is, counting my rows in this SQL statement, as well as want to retrieve the data into the Repeater. This is ASP.NET / C#.

SELECT COUNT(*) AS 'numbersOfOrdres' 
FROM customers 
INNER JOIN orders ON customers.customer_id = orders.order_customer_id

Doesn't seems to work, i'll keep getting the error "Does not contain 'THEN MY FIELD NAME' in the Repeater.

EDIT: Repeater code

<asp:Repeater ID="customerOrdersRepeater" runat="server">
<HeaderTemplate>
    <table>
    <tr>
        <td><b>Customer name</b></td><td><b>Customer adresse</b></td><td>Numbers of orders</td>
    </tr>
</HeaderTemplate>
<ItemTemplate>
    <tr>
        <td><%# Eval("customer_name") %></td><td><%# Eval("customer_adresse") %></td><td><%# Eval("numbersOfOrdres") %></td>
    </tr>
</ItemTemplate>
<FooterTemplate>
    </table>
</FooterTemplate>

Upvotes: 0

Views: 192

Answers (2)

Robin
Robin

Reputation: 2616

If I understand what you're trying to do I think you need to use a group by.

This is what I think you want to see

Customer 1, customer_1_address, 3
Customer 2, customer_2_address, 1
Customer 3, customer_3_address, 5

assuming 3 customers, with 3, 1 and 5 orders respectively.

If so, a group by will likely get what you want, ie

select 
  customer_name,
  customer_address,
  count(*)
from
  customers
join
  orders
on customers.cust_id = orders.cust_id
group by customer_name, customer_address

There may be more efficient ways of stating the group by, ie grouping on the cust_id in a su query then joining on that to get the customer names and addresses. Assuming the above select works for you though I'll leave further improvements to yourself.

Upvotes: 1

detaylor
detaylor

Reputation: 7280

You would need to aggregate your results using GROUP BY. It looks like you want a row per unique customer/customer address. So assuming these are held in customers.name and customers.adresse your query would be

SELECT customers.name AS customer_name, customers.adresse AS customer_adresse, 
    COUNT(*) AS 'numbersOfOrdres' 
FROM customers 
INNER JOIN orders ON customers.customer_id = orders.order_customer_id
GROUP BY customers.name, customers.adresse

Upvotes: 3

Related Questions