SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Group by formats
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

magmo
Aged Yak Warrior

520 Posts

Posted - 04/04/2013 :  03:23:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/04/2013 :  03:42:24  Show Profile  Reply with Quote

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)
Go to Top of Page

magmo
Aged Yak Warrior

520 Posts

Posted - 04/04/2013 :  04:06:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/04/2013 :  05:11:24  Show Profile  Reply with Quote
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

520 Posts

Posted - 04/04/2013 :  05:34:00  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/04/2013 :  05:58:04  Show Profile  Reply with Quote

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

520 Posts

Posted - 04/04/2013 :  06:07:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/04/2013 :  06:08:14  Show Profile  Reply with Quote
--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)

Edited by - bandi on 04/04/2013 06:11:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/04/2013 :  06:10:10  Show Profile  Reply with Quote
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

520 Posts

Posted - 04/04/2013 :  06:27:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/04/2013 :  06:36:18  Show Profile  Reply with Quote
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

520 Posts

Posted - 04/04/2013 :  06:49:06  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/04/2013 :  06:51:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/04/2013 :  06:55:23  Show Profile  Reply with Quote
GROUP BY Format, DATEADD(dd, DATEDIFF(dd, 0, DateAdded), 0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/04/2013 :  07:02:18  Show Profile  Reply with Quote
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

520 Posts

Posted - 04/04/2013 :  07:13:37  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/04/2013 :  07:24:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/04/2013 :  07:25:21  Show Profile  Reply with Quote
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

520 Posts

Posted - 04/04/2013 :  07:27:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/04/2013 :  07:30:10  Show Profile  Reply with Quote
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

520 Posts

Posted - 04/04/2013 :  08:13:11  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000