query help

johnnyg123
johnnyg123 used Ask the Experts™
on
I have the following access table


Shop   Month    Sales
1000    1           1000
1000    2           2000
2000    1           4000
2000    2           5000

Basically there is a list of shops with sales for different months

I am trying to write a query that will list the shops and the sales
for each month across a single line
(all shops will have the same number of sales month entries)



For the sample data above I was hoping for the following



shop month1  month2
1000  1000     2000
2000  4000     5000
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
this will do, you can add more values for "month" as needed.
select shop
  , sum(case when month=1 then Sales end) month1
  , sum(case when month=2 then Sales end) month2
 from yourtable
 group by shop

Open in new window

Top Expert 2016

Commented:
in access

select shop
, sum(iif(month=1,[Month])) as  Month1
, sum(iif(month=2,[Month])) as  Month2
from table
group by shop
Top Expert 2016
Commented:
in access, to make it dynamic, use a crosstab query

TRANSFORM Sum(Table1.[Sales]) AS SumOfSales
SELECT Table1.[shop], Sum(Table1.[Sales]) AS [Total Of Sales]
FROM Table1
GROUP BY Table1.[shop]
PIVOT "Month" & Table1.[Month];
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
my table name is table1 so I tried

select shop  , sum(case when month=1 then Sales end)  , sum(case when month=2 then Sales end)
 from table1
 group by shop

got syntax error (missing operator) in query expression ' sum(case when month=1 then sales end)'
Top Expert 2016

Commented:
johnnyg123,

are you doing this in Access or SQLServer?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
ms access:
select shop  , sum(iif(month=1,Sales,0)  , sum(iif(month=2,Sales,0)
 from table1
 group by shop

Open in new window

Commented:
For Access use the Cross tab query shown to you by Capricorn1.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial