Estoy tratando de hacer una consulta donde se suman los totales de los pedidos de cada cliente.

He intentado un par de maneras diferentes, pero no estoy seguro de la forma correcta de hacerlo.

He probado…

SELECT *
FROM Orders
SUM(Total) as Totals
COUNT(OrderID) as OrderAmt
GROUP BY CustomerID, OrderAmt, ShipName, Totals

Quiero conseguir este resultado….

=====================================
|CustomerID|Orders |ShipName|Total  |
|==========|=======|========|=======|
|3334      |3      |Joe Blow|1100.00|
|---------- | ------- | -------- | -------|
|114       |2      |Steve   |280.00 |
|---------- | ------- | -------- | -------|
|1221      |1      |Sue     |250.00 |
|---------- | ------- | -------- | -------|
|3444      |1      |Bob     |22.00  |
=====================================

De esta tabla…

|===================================|
|CustomerID|OrderID|ShipName|Total  |
|==========|=======|========|=======|
|3334      |232    |Joe Blow|400.00 |
|---------- | ------- | -------- | -------|
|3334      |234    |Joe Blow|500.00 |
|---------- | ------- | -------- | -------|
|3334      |231    |Joe Blow|200.00 |
|---------- | ------- | -------- | -------|
|114       |235    |Steve   |250.00 |
|---------- | ------- | -------- | -------|
|114       |239    |Steve   |30.00  |
|---------- | ------- | -------- | -------|
|1221      |244    |Sue     |250.00 |
|---------- | ------- | -------- | -------|
|3444      |632    |Bob     |22.00  |
|===================================|

Lo que sería la correcta instrucción SQL para esto.

  • GROUP BY + SUM + COUNT
InformationsquelleAutor Monty | 2012-03-05

2 Comentarios

  1. 24

    Sum y count puede ser utilizado para obtener el resultado que usted desea:

    select CustomerID, count(*) as Orders, ShipName, sum(Total) as Total
    from Table
    group by CustomerID, ShipName
    order by count(*) desc;
    • +1 para incluso tomar el tiempo para responder cuando parece que el OP no intente nada
  2. 2
    select CustomerID, count(OrderID) Orders, ShipName, sum(Total) Total
    from Order_TAB
    group by CustomerID, ShipName

Dejar respuesta

Please enter your comment!
Please enter your name here