CODEWITHSUNDEEP
sqlms-access-2010
Hemant
Hemant

Reputation: 97

SQL Aggregate value in column datewise

Below is my database values.

db_values

I am looking for sum of values in column header datewise and I have MS Access database. Can anyone help me to build SQL query? Please find below expected output.

output

Upvotes: 1

Views: 97

Answers (2)

Parfait
Parfait

Reputation: 107567

In case dates are potentially in the hundreds that a conditional aggregate pivot is not feasible, consider a crosstab query, a unique SQL operation available in the Jet/ACE SQL dialect (not in other RDMS).

TRANSFORM SUM(t.val) AS SumOfVal
SELECT t.A, t.B, t.C, t.D
FROM MyTable t
GROUP BY t.A, t.B, t.C, t.D
PIVOT t.Store_date;

Do note: there is a limit of 255 columns in any Access table/query. Hence, if your table results in 250+ unique dates, you will have to split up into multiple crosstabs. You can filter dates accordingly with the PIVOT ... IN clause:

TRANSFORM SUM(t.val) AS SumOfVal
SELECT t.A, t.B, t.C, t.D
FROM MyTable t
GROUP BY t.A, t.B, t.C, t.D
PIVOT t.Store_date IN ('17-09-2016', '18-09-2016', '20-09-2016');

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you know the set of dates, then you can use conditional aggregation. In MS Access this uses the iif() function. Your dates look like strings, so:

select a, b, c, d,
       sum(iif(store_date = '17-09-2016', val, 0)) as [17-09-2016],
       sum(iif(store_date = '18-09-2016', val, 0)) as [18-09-2016],
       sum(iif(store_date = '19-09-2016', val, 0)) as [19-09-2016],
       sum(iif(store_date = '20-09-2016', val, 0)) as [20-09-2016]
From t
Group by a, b, c, d;

You should store dates in the database using the built-in date data type. However, if you must store them as strings, I would strongly encourage you to use the ISO standard YYYY-MM-DD format. This is useful because you can use it for ordering and for range comparisons.

Upvotes: 2

Related Questions