Group by clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by clause

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 06:21:58

hey guys

ineed some help with a group by clause

I have created a query, as seen below, and its nearly returning the data which i need.

Select top 100 FDMSAccountNo_First9,hst_merchnum,hst_sales_amt,hst_date_processed
From Fact_Financial_History
inner join Dim_Outlet
on hst_merchnum = FDMSAccountNo_First9

This qwuery returns the following results,

FDMSAccountNo_First9, hst_merchnum ,hst_sales_amt hst_date_processed
878000015 878000015 922.58 2009-11-01
878000015 878000015 101.54 2009-11-01
878000015 878000015 0.00 2009-11-01
878000015 878000015 2593.3799 2009-11-01
878000015 878000015 413.81 2009-11-01
878000015 878000015 17.49 2009-11-01
878000015 878000015 611.19 2009-11-01
878000015 878000015 137.87 2009-11-01
878000015 878000015 12.61 2009-11-01
878000015 878000015 2062.77 2009-12-01
878000015 878000015 125.56 2009-12-01
878000015 878000015 60.00 2009-12-01
878000015 878000015 41.15 2009-12-01

i then created a second query

select top 100 hst_merchnum,sum(hst_sales_amt)as 'Total'
from [FDMS].[dbo].[Fact_Financial_History]
Group by hst_merchnum

which returned the following results

hst_merchnum Total
878000015 64131.3001
878000016 1112873.16
878000023 698.83
878000024 5355.08
878000028 18484.24
878000036 3413.76
878000039 77793.1301
878000040 3676.70
878000043 5329.03
878000044 1050.43
878000046 42693.17
878000047 84298.25
878000050 11015.14

As you can see the second query, has grouped the hst_merchnum together and gave me the total hst_sales_amt.

What i need is a query, to group the hst_merchnum together, and provide me a monthly break down of the hst_sales_amt

Can anyone help me with this ?
As i spent the last week on it, and have progressed any further :(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 06:42:30

Add another column to the group by clause (and select list) as shown in red:

SELECT
	TOP 100 hst_merchnum,
	DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
	SUM(hst_sales_amt) AS 'Total'
FROM
	[FDMS].[dbo].[Fact_Financial_History]
GROUP BY
	hst_merchnum,
	DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)

If you don't care to group by hst_merchnum, remove that from the select list and group by clause.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 06:44:21

hi

Thank you for your response, i am new to sql so please be patient

Do you mean have the query like this

select top 100 hst_merchnum,sum(hst_sales_amt)as 'Total'
from [FDMS].[dbo].[Fact_Financial_History]
Group by hst_merchnum
SELECT
TOP 100 hst_merchnum,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
SUM(hst_sales_amt) AS 'Total'
FROM
[FDMS].[dbo].[Fact_Financial_History]
GROUP BY
hst_merchnum,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 06:46:56

And i have to group by hst_merchnum

Realistcally i would like the data to look something like
for eg

hst_merchnum jan feb march
878000015 10000 2000 15000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 06:53:20

No, I meant just the second query - i.e., the query that I posted by iteself.

I am making the assumption that the column hst_date_processed is in the table [FDMS].[dbo].[Fact_Financial_History]. If that is not the case, the query I posted will give you an error when you try to run it. If the hst_date_processed is in the Dim_Outlet table, then you will need to join on that table like this:

SELECT
	TOP 100 hst_merchnum,
	DATEADD(DAY, DATEDIFF(DAY, 0, hst_date_processed), 0) AS FirstDayOfMonth,
	SUM(hst_sales_amt) AS 'Total'
FROM
	Fact_Financial_History
	INNER JOIN Dim_Outlet
		ON  hst_merchnum = FDMSAccountNo_First9
GROUP BY
	hst_merchnum,
	DATEADD(DAY, DATEDIFF(DAY, 0, hst_date_processed), 0)

But my guess is that you don't need to do this because the sales date is likely in the same table that has the sales amount. If the first query runs correctly ignore this.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 06:58:28

Hi Sunitabeck

hst_date_processed is from the [FDMS].[dbo].[Fact_Financial_History] table , and your query runs and returns results.

at present the query returns the following results.

hst_merchnum FirstDayOfMonth Total
878594477 2009-04-01 00:00:00.000 6424.74
878545390 2010-02-01 00:00:00.000 4062.11
878596662 2010-03-01 00:00:00.000 33622.9201
878143202 2010-05-01 00:00:00.000 13613.17
878097520 2010-10-01 00:00:00.000 7631.50
878720093 2011-03-01 00:00:00.000 33195.96
878160215 2011-06-01 00:00:00.000 19122.39
878705209 2011-11-01 00:00:00.000 2283.17
878738494 2012-01-01 00:00:00.000 247.60

How can i get the query to return hst_sales_amt per month per hst_merchnum? and it going along in columns
like in my example posted above ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 07:16:21

The result is showing one row for each hst_merchnum and each month (it is just showing the first day of the month).

As to getting it displayed in a column-wise fashion, if at all possible, the best thing to do would be to do that in a client application or front-end if you have one. For example, if you are using SQL reporting services, it can consume the output of the query that you posted and pivot it easily to show column-wise data.

If you don't have a front-end, or if doing it there is not a possibility, it can be done in SQL, but generally experts recommend against it. If you do want to do it in SQL, do you know how many columns you will have (ie, how many months)? From your sample data it looks like it spans from 2009 to 2012.

If you don't know in advance how many columns or what date ranges, you will need to use dynamic SQL to do the pivoting. Madhivanan's blog has the code and examples: http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 07:16:42

i can see that your query returns it via month and sales amount, which is great. however its not the layout which i want

For instance i changed your query to look a specific hst_merchnum. See below

SELECT
TOP 100 hst_merchnum,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
SUM(hst_sales_amt) AS 'Total'
FROM
[FDMS].[dbo].[Fact_Financial_History]
where hst_merchnum ='878594479'
GROUP BY
hst_merchnum,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)

And the results it returned is as follows

hst_merchnum FirstDayOfMonth Total
878594479 01/01/2009 275.28
878594479 01/02/2009 250.24
878594479 01/03/2009 2345.66
878594479 01/04/2009 2264.31
878594479 01/05/2009 6254.6998
878594479 01/06/2009 580.24
878594479 01/07/2009 1853.19
878594479 01/08/2009 339.41
878594479 01/09/2009 1012.51
878594479 01/10/2009 1119.99
878594479 01/11/2009 1325.71
878594479 01/12/2009 600.48
878594479 01/01/2010 188.93
878594479 01/02/2010 1407.03
878594479 01/03/2010 870.81
878594479 01/04/2010 2338.09
878594479 01/05/2010 2179.81
878594479 01/06/2010 3188.25
878594479 01/07/2010 1758.98
878594479 01/08/2010 1365.41
878594479 01/09/2010 715.61
878594479 01/10/2010 1219.15
878594479 01/11/2010 2639.12
878594479 01/12/2010 1264.42
878594479 01/01/2011 1692.73
878594479 01/02/2011 2407.12
878594479 01/03/2011 1411.76
878594479 01/04/2011 1324.14
878594479 01/05/2011 1450.29
878594479 01/06/2011 1553.64
878594479 01/07/2011 1857.58
878594479 01/08/2011 1063.56
878594479 01/09/2011 2301.05
878594479 01/10/2011 2815.79
878594479 01/11/2011 1405.66
878594479 01/12/2011 914.16
878594479 01/01/2012 754.28
878594479 01/02/2012 1887.41
878594479 01/03/2012 3339.14
878594479 01/04/2012 3455.87
878594479 01/05/2012 713.25
878594479 01/06/2012 2038.51
878594479 01/07/2012 1147.16

First of all i am only interested in 2012 dates, is there anyway we can apply a filter where it only looks at total in 2012 ?

Also instead of it providing me with a massive list going down, can we change the format to columns ?

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 07:20:46

hi sunitabeck

Thank you for your post, we do have SQL reporting services, but i am not up to speed on how to use such tool. Thats why i thought it be easier to build it within SQL

What i am trying to achieve is look at every hst_merchnum, use hst_date_processed and break the hst_sales_amt down per month via hst_date_processed and just look 2012 transactions. 2012 transactions will use hst_date_processed

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 07:27:33

If you are just interested in 2012, that is more manageable - do it like shown below. You need to be on SQL 2005 or later for this to work.

SELECT
	*
FROM
(
	SELECT
		TOP 100 hst_merchnum,
		DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
		SUM(hst_sales_amt) AS 'Total'
	FROM
		[FDMS].[dbo].[Fact_Financial_History]
	WHERE
		hst_date_processed >= '20120101' AND hst_date_processed < '20130101'
	GROUP BY
		hst_merchnum,
		DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)	
) s	
PIVOT
( MAX(amt) FOR dt IN (
	[01/01/2012],[01/02/2012],[01/03/2012],[01/04/2012],[01/05/2012]
	,[01/06/2012],[01/07/2012],[01/08/2012],[01/09/2012],[01/10/2012]
	,[01/11/2012],[01/12/2012]))p

Those dates in the PIVOT query should be in the same format as what you are getting from the inner query.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 07:32:57

hi sunitabeck

I am running sql server 2008 r2, however the code you provide doesnt seem to work

i am getting the following error msg

Msg 207, Level 16, State 1, Line 18
Invalid column name 'amt'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'dt'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 07:38:31

Sorry my bad. Make the changes shown in red:

...
) s	
PIVOT
( MAX(Total) FOR FirstDayOfMonth IN (
	[01/01/2012],[01/02/2012],[01/03/2012],[01/04/2012],[01/05/2012]
...

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 07:44:57

hi sunitabeck

Thank you very much for your help
You are a sql guru, i have spent around 100hours trying to resolve this :)

i owe you huges

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 08:04:47

Thank you for the kind words, glad it worked out for you.

But, if some of the real experts on this forum were answering the question, you would have gotten the right answer at the first attempt instead of the multiple attempts we had to go through. And, no I am not being falsely modest; some of them are very perceptive and can read between the lines to discern what you are really after !! :)

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 08:07:44

well i should of been 100% clearer in my instructions, so sorry for any confusion or Inconvenience caused

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 08:16:19

One more question, why did you use DATEADD(DAY,DATEDIFF(DAY ?
Can you explain what this function within this query does ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 08:23:51

You will see what it does if you run these queries. It is a way to strip of less significant parts from a datetime. So if you wanted to group by month, you would use the third one that strips of the date and month.

--1 gets the current date and time
SELECT GETDATE(); 

--2 removes the time part and gives you the beginning of the day
SELECT DATEADD(DAY,DATEDIFF(DAY,0,getdate()),0); 


--3 removes the time and day part and gives you the beginning of the month
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0);

--4 removes the time, day and month part and gives you the beginning of the month
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 08:29:57

Come to think of it, if you want to group by month, you should be using

DATEADD(MONTH,DATEDIFF(MONTH,0,hst_date_processed),0)

instead of

DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 09:17:21

hi

well i have adapted the code to

SELECT
*
FROM
(
SELECT
TOP 10000 o.Agent_Chain_No,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
SUM(hst_sales_amt) AS 'Total'
FROM
[FDMS].[dbo].[Fact_Financial_History]
inner join Dim_Outlet o
on hst_merchnum = FDMSAccountNo_First9
WHERE
hst_date_processed >= '20120101' AND hst_date_processed < '20130101'
and RM_Code not like 'NA'

GROUP BY
o.Agent_Chain_No,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)
) s
PIVOT
( max(Total) FOR FirstDayOfMonth IN ([20120101],[20120201],[20120301],[20120401],[20120501]
,[20120601],[20120701],[20120801],[20120901],[20121001]
,[20121101],[20121201]))p

At first as the months were in the correct format, it was giving me alot of null values which i had to change. but expect for that it worked a treat .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources