MySQL查询特定的年份和地区

I try to make a query, where I can see the netamount of sales for the US and rest of the World for the years 2007 and 2008 in the database along with all subtotals as well as the grand total.(It's to mention, that US and World are regions, which are already coded in the database)

我尝试进行查询,在那里我可以看到数据库中2007年和2008年美国和世界其他地区的销售净额以及所有小计以及总计。(这就是说,美国和世界是已经在数据库中编码的区域)

My query as far:

我的查询到目前为止:

SELECT country regionname, sum(custamot)Totsales 
FROM( select o.customerid, sum(netamount) custamot, c.country
FROM orders o join customers c
on o.customerid = c.customerid
where country like 'US' 
group by c.customerid ) iv;

This query just tells me the totsales for US(without a year as you can see). I tried the query without the part:

这个查询告诉我美国的totsales(没有一年,你可以看到)。我尝试了没有部分的查询:

where country like 'US'

but the result is the same. I can't find a query where it shows me the US and the World for a specific year...

但结果是一样的。我无法找到一个查询,它向我展示特定年份的美国和世界......

Any idea would be helpful

任何想法都会有所帮助

gratefully, JOP

3 个解决方案

#1

You need to add a group by clause on the outside:

您需要在外部添加group by子句:

SELECT country as regionname, sum(custamot) as Totsales 
FROM (select o.customerid, sum(netamount) custamot, c.country
      FROM orders o join
           customers c
           on o.customerid = c.customerid
      group by c.customerid
     ) iv
group by country;

Actually, you don't need the subquery:

实际上,您不需要子查询:

select country as regionname, sum(netamount) as Totsales
FROM orders o join
     customers c
     on o.customerid = c.customerid
group by country;

And to get the total in the same query use with rollup:

并使用汇总获取相同查询中的总数:

select country as regionname, sum(netamount) as Totsales
FROM orders o join
     customers c
     on o.customerid = c.customerid
group by country with rollup;

And finally, to add the year, either use a where clause or try this:

最后,要添加年份,请使用where子句或尝试:

select year(orderdate) as yr, country as regionname, sum(netamount) as Totsales
FROM orders o join
     customers c
     on o.customerid = c.customerid
group by year(orderdate), country with rollup;

And to separate US and ROW:

并将美国和行分开:

select year(orderdate) as yr,
       (case when country = 'US' then 'US' else 'ROW' end) as region,
       sum(netamount) as TotSales
FROM orders o join
     customers c
     on o.customerid = c.customerid
group by year(orderdate),  (case when country = 'US' then 'US' else 'ROW' end) with rollup;

#2

Something like this:

像这样的东西:

SELECT country as regionname, sum(custamot)Totsales 
FROM( select o.customerid, sum(netamount) custamot, c.country
      FROM orders o join customers c
       on o.customerid = c.customerid
      where left(my_date,4) between 2007 and 2008
      group by c.customerid ) iv
group by country

Saludos ;)

#3

To get the results for a certain year, assuming you have a date field,

要获得特定年份的结果,假设您有一个日期字段,

where YourDateField >= {d '2007-01-01'}
and YourDateField < {d '2008-01-01'}

#1