Count items in column by date? (SQL)

Question

1.00/5 (5 votes)

See more:

Hello!
I am using Microsoft SQL Server 2005
I have a table like this:

PHP

QueueNo      Date
 3B001     5/14/2014
 3B002     5/13/2014 
 3B003     5/13/2014
 3G004     5/12/2014
 3G005     5/12/2014
 2P001     5/12/2014
 2G002     5/12/2014
 2P003     5/14/2014  
 1P001     5/12/2014

i used this code for the table above:

PHP

SELECT     queueno, CONVERT(varchar(10), qdate, 101) AS DateToday
FROM         TRN_Queue

I want to make a new table that counts all the ones that start with '3','2', and '1' but buy date.
something like this:

PHP

   Date     QueueInitial    Count
5/14/2014         3           1
5/14/2014         2           1
5/13/2014         3           2
5/12/2014         3           2
5/13/2014         2           2
5/13/2014         1           1

Super thanks in advance, like really! HAHA :)

Posted 14-May-14 23:06pm

charliedev

Add a Solution

Comments

_Zorro_ 15-May-14 5:14am

What do you mean by: a new table?

charliedev 15-May-14 20:23pm

i mean column, new column :/ that shows count

Corporal Agarn 15-May-14 7:59am

Hint: GROUP BY

charliedev 15-May-14 20:22pm

i tried it but it wont work. this is my code:

SELECT LEFT(queueno, 1) AS QueueInitial, COUNT(*) AS Count, CONVERT(varchar(10), qdate, 101) AS DateToday
FROM TRN_Queue
GROUP BY qdate

this is error:
TRN_Queue.queueno is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Corporal Agarn 16-May-14 7:52am

You forgot LEFT(queueno,1) in the group by

2 solutions

Solution 2

I agree with OriginalGriff, try looking at this query and see if you can adapt it to your needs though:

SQL

DECLARE @TRN_Queue TABLE
(
	QueueNo NVARCHAR(5),
	[Date] DATETIME2
);

INSERT INTO @TRN_Queue VALUES('3B001', '5-10-2014')
INSERT INTO @TRN_Queue VALUES('3B002', '5-09-2014')
INSERT INTO @TRN_Queue VALUES('3B003', '5-09-2014')
INSERT INTO @TRN_Queue VALUES('3G004', '5-11-2014')
INSERT INTO @TRN_Queue VALUES('3G005', '5-11-2014')
INSERT INTO @TRN_Queue VALUES('2P001', '5-11-2014')
INSERT INTO @TRN_Queue VALUES('2G002', '5-11-2014')
INSERT INTO @TRN_Queue VALUES('2P003', '5-08-2014')
INSERT INTO @TRN_Queue VALUES('1P001', '5-11-2014')

SELECT SUBSTRING(QueueNo, 1, 1), COUNT(SUBSTRING(QueueNo, 1, 1)), COUNT(SUBSTRING(QueueNo, 1, 1)) OVER() FROM @TRN_Queue
GROUP BY SUBSTRING(QueueNo, 1, 1)

Hope it helps...

Posted 15-May-14 22:55pm

_Zorro_

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-05-14T23:12:00

So take teh anser to your previous question: Count items in column? (SQL)[^] and look at how it works. It's a pretty simple modification to that to do this, and learning how to do it yourself will save you a lot of time in the future - it really is quicker to do it yourself than to ask someone else to do it for you - and it prevents you getting "abuse" marked as someone who gets us to do all his work in little bits. We do expect you to think about what you are doing! (And getting marked as abusive will very likely get your account banned)