question

sraminz avatar image
sraminz asked

Problem in SQL query with GROUP BY

Hi guys.

I have a table in sql server like this:

Insurance_DateOfDefinition Insurance_FreeCardNo Insurance_UsedStatus
1398/05/01 1000001 True
1398/05/01 1000002 True
1398/05/01 1000003 False
1398/05/01 1000004 False
1398/05/01 1000005 False
1398/05/01 1000006 False
1398/05/01 1000007 True
1398/05/01 1000008 False
1398/05/01 1000009 False
1398/05/01 1000010 True
1398/06/01 1000011 True
1398/06/01 1000012 True
1398/06/01 1000013 True
1398/06/01 1000014 False
1398/06/01 1000015 False
1398/06/01 1000016 False
1398/06/01 1000017 False
1398/06/01 1000018 False

For Insurance_UsedStatus, 1 means the card number is USED and 0 Means it's free.

I need a query to give me the following result:

Insurance_DateOfDefinition NoOfRegisteredCards_InThisDate NoOfFreeCards_InThisDate
1398/05/01 10 6
1398/06/01 8 5

I tried the command below:

SELECT
Insurance_DateOfDefinition
,COUNT (Insurance_FreeCardNo) NoOfRegisteredCards_InThisDate
, (select COUNT (Insurance_FreeCardNo) FROM InsuranceFreeCardNo where Insurance_UsedStatus=1) NoOfFreeCards_InThisDate
FROM InsuranceFreeCardNo
group by Insurance_DateOfDefinition

The result was:

Insurance_DateOfDefinition NoOfRegisteredCards_InThisDate NoOfFreeCards_InThisDate
1398/05/01 10 7
1398/06/01 8 7

As you see, the column NoOfFreeCards_InThisDategive takes the wrong values.
Would you please help me with this problem?
Thank you in advance.
Rara.

sql query
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Try this

SELECT 
 Insurance_DateOfDefinition,
 COUNT(Insurance_FreeCardNo) NoOfRegisteredCards_InThisDate, 
 COUNT (Insurance_FreeCardNo)- SUM(Insurance_UsedStatus) NoOfFreeCards_InThisDate
FROM InsuranceFreeCardNo
group by Insurance_DateOfDefinition
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sraminz avatar image sraminz commented ·

Thank you for your attention and answer, dude. But the last field is not an integer value. It is Boolean. It's defined as BIT in SQL server database.

1 stands for True and 0 stands for False. I updated the question to make it clear.

Every other answers are greatly appreciated.

Rara.

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·

That's no problem, cast it as an INT

COUNT (Insurance_FreeCardNo)- SUM(cast(Insurance_UsedStatus as INT)) NoOfFreeCards_InThisDate
0 Likes 0 ·
sraminz avatar image sraminz commented ·

It worked.

So smart & clever!

Thank you.

I wanted to like your answer, but sqlservercentral.com told me:

"We're sorry, but this cannot be voted up by you until you have at least 15 reputation."

Anyway A VERY BIG LIKE for you.

Rara.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.