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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Group by formats

Author  Topic 

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 03:23:09
Hi

I have this table and data...


USE [Test]
GO
/****** Object:  Table [dbo].[tbl_test]    Script Date: 2013-04-04 09:14:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TransactionID] [nvarchar](255) NULL,
	[NumberOfCards] [int] NULL,
	[DateAdded] [datetime] NULL,
	[IsFetched] [bit] NULL,
	[Duplicate] [bit] NULL,
	[Format] [nvarchar](5) NULL,
 CONSTRAINT [PK_tbl_test] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[tbl_test] ON 

GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (1, N'123', 1, CAST(0x0000A17400BC2378 AS DateTime), 1, 0, N'A5')
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (2, N'123', 1, CAST(0x0000A17400BC2378 AS DateTime), 0, 1, N'A5')
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (3, N'456', 2, CAST(0x0000A17400D83C71 AS DateTime), 1, 0, N'A4')
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (4, NULL, 1, CAST(0x0000A17400F3306C AS DateTime), 1, 0, N'A4')
GO
INSERT [dbo].[tbl_test] ([ID], [TransactionID], [NumberOfCards], [DateAdded], [IsFetched], [Duplicate], [Format]) VALUES (5, N'0', 1, CAST(0x0000A17400F41889 AS DateTime), 1, 0, N'A4')
GO
SET IDENTITY_INSERT [dbo].[tbl_test] OFF
GO




I would like a query that could return a result that group the different formats, day and also by free and paid, it should be classified as paid if TransactionID is <> '', if its NULL, 0 or '' then it should be classified as free. It should also filter out only the ones that have "IsFetched" = 1 and "Duplicate" = 0, something like this..

QtyFree QtyPaid Day Format
2 1 2013-03-01 A4
0 1 2013-03-01 A5


Can someone please show me how this could be done?

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-04-04 : 03:42:24
[code]
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN Format END)AS QtyPaid,
COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN Format END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
[/code]
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 04:06:36
Excellent, if I want to have the same conditions but count by "NumberOfCards" instead of TransactionID, how would that be?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-04-04 : 05:11:24
Here we are counting formats, not the Transaction...
The following query is for getting count of NumberOfCards based on TransactionId rules
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards END)AS QtyPaid,
	COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 05:34:00
quote:
Originally posted by bandi

Here we are counting formats, not the Transaction...
The following query is for getting count of NumberOfCards based on TransactionId rules
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards END)AS QtyPaid,
	COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)




Hi


If I change to SUM instead of count it seem to almost work as I meant, but I in the A5 format case I then get NULL instead of 0 for the QtyFree column.


This is what I changed to..


SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards END)AS QtyPaid,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-04-04 : 05:58:04
[code]
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 06:07:50
quote:
Originally posted by visakh16


SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)



Thanks, that worked, but now I see a new problem. If the TransactionID column contain a value like this "12345_69" then I get a "Conversion failed when converting the nvarchar value '12345_69' to data type int." error.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-04-04 : 06:08:14
--Alternate is 
SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)

EDIT: Put 1 instead of NumberOfCards column

SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN 1 ELSE 0 END)AS QtyPaid,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN 1 ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-04-04 : 06:10:10
quote:
Originally posted by magmo

quote:
Originally posted by visakh16


SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)



Thanks, that worked, but now I see a new problem. If the TransactionID column contain a value like this "12345_69" then I get a "Conversion failed when converting the nvarchar value '12345_69' to data type int." error.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	SUM(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
	SUM(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 06:27:32
quote:
Originally posted by visakh16

quote:
Originally posted by magmo

quote:
Originally posted by visakh16


SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
	SUM(CASE WHEN NULLIF(TransactionID, 0) IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)



Thanks, that worked, but now I see a new problem. If the TransactionID column contain a value like this "12345_69" then I get a "Conversion failed when converting the nvarchar value '12345_69' to data type int." error.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




SELECT Format, CAST(DateAdded AS DATE) DateAdded,
	SUM(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
	SUM(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
GROUP BY Format, CAST(DateAdded AS DATE)



That works fine, but if I run it in SQL Server 2005 I had to change DATE to DATETIME but then I doesn't get it grouped by the date, guess thats beacuse of the use of DATETIME, I would like them to be grouped by the date.

This is how I changed it...


SELECT Format, CAST(DateAdded AS DATETIME) DateAdded,
	SUM(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards ELSE 0 END)AS QtyPaid,
	SUM(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards ELSE 0 END)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0 AND (DateAdded BETWEEN CONVERT(DATETIME, '2013-03-01', 102) AND CONVERT(DATETIME, '2013-03-03', 102))
GROUP BY Format, CAST(DateAdded AS DATETIME)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-04-04 : 06:36:18
May be this?
SELECT Format, MAX(DateAdded)DateAdded,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, CAST(DateAdded AS DATE)
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 06:49:06
quote:
Originally posted by bandi

May be this?
SELECT Format, MAX(DateAdded)DateAdded,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, CAST(DateAdded AS DATE)




Sorry but I cannot use DATE I have to use DATETIME, I get a "Type Date is not a defined system type.
" if I use that
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-04-04 : 06:51:40
quote:
Originally posted by magmo

quote:
Originally posted by bandi

May be this?
SELECT Format, MAX(DateAdded)DateAdded,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, CAST(DateAdded AS DATE)




Sorry but I cannot use DATE I have to use DATETIME, I get a "Type Date is not a defined system type.
" if I use that


use

SELECT Format, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0),
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0)


see

http://visakhm.blogspot.in/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-04-04 : 06:55:23
GROUP BY Format, DATEADD(dd, DATEDIFF(dd, 0, DateAdded), 0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-04-04 : 07:02:18
Please be aware that using COUNT will generate warnings like below which causes issues in application side while executing queries

Null value is eliminated by an aggregate or other SET operation.

Whereas SUM will have very little chance of generating them


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 07:13:37
quote:
Originally posted by visakh16

quote:
Originally posted by magmo

quote:
Originally posted by bandi

May be this?
SELECT Format, MAX(DateAdded)DateAdded,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, CAST(DateAdded AS DATE)




Sorry but I cannot use DATE I have to use DATETIME, I get a "Type Date is not a defined system type.
" if I use that


use

SELECT Format, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0),
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaid,
	ISNULL(COUNT(CASE WHEN NULLIF(TransactionID, '0') IS NULL THEN NumberOfCards END), 0)AS QtyFree
FROM tbl_test
WHERE IsFetched = 1 AND Duplicate = 0
AND DateAdded >='2013-03-01' AND DateAdded <'2013-03-04'
GROUP BY Format, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0)



This is great, but shouldn't the time part be stripped of, it seem that it should if I check your page.

see

http://visakhm.blogspot.in/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-04-04 : 07:24:19
the timepart value would be stripped off and only 00:00:00 will remain

datetime always represents timepart also so if you want to strip off 00:00:00 part then you need to do it using formatting function in your front end application or using CONVERT in t-sql (if no front end is present)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-04-04 : 07:25:21
While grouping only we have stripped of Time part from DateAdded Column...
Whats the problem now?
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 07:27:08
quote:
Originally posted by visakh16

the timepart value would be stripped off and only 00:00:00 will remain

datetime always represents timepart also so if you want to strip off 00:00:00 part then you need to do it using formatting function in your front end application or using CONVERT in t-sql (if no front end is present)

Thank you all for the help, and thanks for the explanation about the time part.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-04-04 : 07:30:10
We can get date part only by using CONVERT function.. (Note: this is NOT recommend)

SELECT Format, convert(varchar(10),DateAdded,121) AS DateAdded
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts
Posted - 2013-04-04 : 08:13:11
Hi again

I thought it would be nice to have an additional approach to this query. If I instead of showing QtyPaid and QtyFree as seperate values and instead have them as one value (QtyPaid + QtyFree as SumTotal) how would that be, I tried just to sum them both but then I got a "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." error...
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site