CODEWITHSUNDEEP
phpmysqlsql
user1320260
user1320260

Reputation:

SQL - Count how many itmes in a GROUP also using SUM

I'm using PDO (still learning) and have the current SQL query. This works perfectly, merging duplicate entries and adding their value. Is there a way I could see how many duplicate entries there were?

$SQL = "SELECT Col1, SUM(Col2) FROM table WHERE Date > :FromDate AND Date < :EndDate GROUP BY Col1";

My table looks like this

Col1     Col2 
-----  ------
ABC       2
ABA       3
ADD       1
AED       3
ABC       2
ABA       3
ADD       1
AED       3
AED       0

At the moment, after I loop through, the result looks like this

Col1    Col2    
----    ----    
ABC       4
ABA       6
ADD       2
AED       6

But i'd like to get the value of how many times it occured in the DB before it was grouped so i could end up with

Col1    Col2    Times Appeared
----    ----    --------------
ABC       4           2
ABA       6           2
ADD       2           2
AED       6           3

Upvotes: 0

Views: 102

Answers (3)

gudatcomputers
gudatcomputers

Reputation: 2882

add COUNT(Col1) as the last thing selected in your query. Why would that not work?

So, I don't know why the downvotes.. unless this is a platform difference, but in SQL Server the following code is equivalent

DECLARE @temptbl TABLE 
(
    textcol varchar(50),
    numcol int
)

INSERT INTO @temptbl (textcol,numcol) VALUES('test',4)
INSERT INTO @temptbl (textcol,numcol) VALUES('test',3)
INSERT INTO @temptbl (textcol,numcol) VALUES('test',2)
INSERT INTO @temptbl (textcol,numcol) VALUES('test2',1)
INSERT INTO @temptbl (textcol,numcol) VALUES('test2',3)

SELECT textcol,SUM(numcol),COUNT(numcol) FROM @temptbl GROUP BY textcol
SELECT textcol,SUM(numcol),COUNT(*) FROM @temptbl GROUP BY textcol

Upvotes: 0

BWS
BWS

Reputation: 3836

Add a COUNT, like this:

SELECT Col1, SUM(Col2), COUNT(*)
FROM table 
WHERE Date > :FromDate 
  AND Date < :EndDate 
GROUP BY Col1

Upvotes: 1

juergen d
juergen d

Reputation: 204756

Use count() for that. It counts only the records in a group if used with group by

SELECT Col1, SUM(Col2), count(*) as 'Times appeared'
FROM table 
WHERE Date > :FromDate AND Date < :EndDate 
GROUP BY Col1"

Upvotes: 2

Related Questions